Sql-server – Count instances of value in multiple tables

sql servert-sql

I'm attempting to write a query that returns the count of a given value across multiple tables.

Right now, I'm using the following query

SELECT COUNT(*) AS SL,
(
  SELECT COUNT(*)
  FROM [Sales Line Archive]
  WHERE [Document No_] = 123
) AS SLA
FROM [Sales Line]
WHERE [Document No_] = 123

Which returns a single result with the count of lines in Sales Line in the first column and the count from Sales Line Archive in the second.

However, I'd like to request multiple records using an IN clause, so that I could get results like the following

Document No_, SL, SLA
1, 3, 0
2, 0, 4
3, 2, 1

Best Answer

One way would be to pivot

WITH CTE AS
(
SELECT [Document No_], 'SL' AS Source
FROM [Sales Line]
UNION ALL
SELECT  [Document No_], 'SLA' AS Source
FROM [Sales Line Archive]
) 
SELECT P.[Document No_],
       P.SL,
       P.SLA
FROM CTE
PIVOT (COUNT(Source) FOR Source IN ([SL], [SLA])) P
WHERE P.[Document No_] IN (1,2,3);

I'd hope that the predicate on [Document No_] gets pushed up into the selects from the source tables. Check the execution plans to be sure.

Related Solutions

Sql-server – MERGE a UNION query results down to one row per KEY value

It seems you just need to use a derived table or common table expression with a new grouping level:

DECLARE 
    @FROM datetime = CONVERT(datetime, '12-30-2013 03:00:00', 101), 
    @TO datetime = CONVERT(datetime, '12-28-2014 03:00:00', 101);

WITH Combined AS
(
    SELECT 
        DivisionName = d.DivisionName, 
        [CC w/o Tips] = (SUM(PaymentAmount) - SUM(TipAmount)), 
        [Tips] = SUM(TipAmount),
        [Receipts] = SUM(PaymentAmount)
    FROM LFLive.POS.OrderPayments AS op
    JOIN LFLive.POS.Orders AS o
        ON o.OrderId=op.OrderId
    JOIN LFLive.POS.Payments AS p
        ON p.PaymentId=op.PaymentId 
    JOIN LFLive.Directory.Divisions AS d 
        ON d.DivisionId=o.DivisionId  
    WHERE 
        op.IsVoided=0 
        AND op.IsDeleted=0 
        AND o.IsVoided=0 
        AND o.IsClosed=1
        AND o.OrderTypeId = 2 
        AND p.PaymentTypeId = 6 
        AND o.DateClosed BETWEEN @FROM AND @TO
    GROUP BY 
        d.DivisionName

    UNION ALL -- Not UNION!

    SELECT 
        DivisionName = d.DivisionName, 
        [CC w/o Tips] = (SUM(PaymentAmount) - SUM(TipAmount)), 
        [Tips] = SUM(TipAmount),
        [Receipts] = SUM(PaymentAmount)
    FROM LFArchive.POS.OrderPayments AS op
    JOIN LFArchive.POS.Orders AS o
        ON o.OrderId=op.OrderId
    JOIN LFLive.POS.Payments AS p
        ON p.PaymentId=op.PaymentId 
    JOIN LFLive.Directory.Divisions AS d 
        ON d.DivisionId=o.DivisionId  
    WHERE 
        op.IsVoided=0 
        AND op.IsDeleted=0 
        AND o.IsVoided=0 
        AND o.IsClosed=1
        AND o.OrderTypeId = 2 
        AND p.PaymentTypeId = 6 
        AND o.DateClosed BETWEEN @FROM AND @TO
    GROUP BY 
        d.DivisionName
)
SELECT
    C.DivisionName,
    [CC w/o Tips] = SUM(C.[CC w/o Tips]),
    [Tips] = SUM(C.Tips),
    [Receipts] = SUM(C.Receipts)
FROM Combined AS C
GROUP BY
    C.DivisionName
ORDER BY 
    C.DivisionName;

I replaced the UNION with UNION ALL because you don't want duplicate removal.

Sql-server – Subtracting from multiple CASE statements

So you want the result of the second SUM to be subtracted from the first SUM. One option would be to do just that:

SELECT TOP (100) PERCENT
  IM.IM_PROD_CODE,
  IV.IV_CUKEY,
  IM.IM_KEY,
  IM.IM_DESCR,
  Sales    = SUM(CASE WHEN IV.IV_TYPE = '1' THEN SH.SH_QTY * SH.SH_PRICE END),
  Credits  = SUM(CASE WHEN IV.IV_TYPE = '8' THEN SH.SH_QTY * PK1.SH.SH_PRICE END),
  NetSales = SUM(CASE WHEN IV.IV_TYPE = '1' THEN SH.SH_QTY * SH.SH_PRICE END)
           - SUM(CASE WHEN IV.IV_TYPE = '8' THEN SH.SH_QTY * PK1.SH.SH_PRICE END)
FROM
  … /* the rest of your query */

Yes, you are repeating the SUM expressions, but that is fine, there are only two of them. The DRY principle applicable to many other languages is less relevant in SQL, where duplication of code can be a perfectly normal way to achieve better performance.

In this case, however, the duplication can be avoided by using a derived table:

SELECT
  IM_PROD_CODE,
  IV_CUKEY,
  IM_KEY,
  IM_DESCR,
  Sales,
  Credits,
  NetSales = Sales - Credits
FROM
  (
    … /* the entirety of your current query */
  ) AS derived;

I would also like to suggest, if I may, that you be consistent in how you specify your constant literals. What I mean is sometimes in your query you are providing the matched values for IV.IV_TYPE as strings (IV.IV_TYPE = '1') and other times as numbers (IV.IV_TYPE = 1). You should really choose one way and, of course, it should be the one matching the column's actual type.

Also, the TOP (100) PERCENT in your query makes little sense. It looks as though it may be a remnant of an old technique for intermediate materialisation, which, however, may no longer be working in your version of SQL Server.

There is also this HAVING filter in your query (HAVING (IM.IM_KEY = 'A-05.000.007.01_LM')), which would work more efficiently if you moved the condition to the WHERE clause.

One last note concerns your second CASE expression (the Type 8 one). One of the columns it is referencing is qualified with an alias that is not found in your FROM clause: PK1.SH.SH_PRICE. I am assuming that is some kind of a copy-paste error posting your script here but I thought I would let you know so that you could correct it.

Best Answer

Related Solutions

Sql-server – MERGE a UNION query results down to one row per KEY value

Sql-server – Subtracting from multiple CASE statements

Related Question