5

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
10

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.

  • 1
    It does! Thanks a ton. I've never worked with PIVOT before, so I'm definitely going to explore its uses. – dstaley May 26 '16 at 18:52
  • Full join using PIVOT? Didn't expect that. – Andriy M May 27 '16 at 6:20
  • 1
    @AndriyM An actual full outer join of the two aggregated partial results could be more efficient as they could come out of a stream aggregate ordered and go into a merge full outer join. – Martin Smith May 27 '16 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.