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

1 Answer 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.

3
  • 1
    It does! Thanks a ton. I've never worked with PIVOT before, so I'm definitely going to explore its uses.
    – dstaley
    Commented May 26, 2016 at 18:52
  • Full join using PIVOT? Didn't expect that.
    – Andriy M
    Commented May 27, 2016 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. Commented May 27, 2016 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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