I came across an interesting problem this week. The requirement was to get row counts from two different tables and return these on the same row. The tables had a common column, lets call it category.
The first thought was to use GROUP BY in T-SQL, but there is a problem with this:
|
1 2 |
SELECT a.Category, COUNT(a.*), b.Category, COUNT(b.*) FROM TableA a INNER JOIN TableB b ON b.Category = a.Category |
The problem with this code is that it gives the cartesian product of A and B, but I would like to know the count of A and the count of B on the same row.
There may well be better ways of doing it, but the way I solved it this time was to use CTE’s (common table expressions). The example T-SQL code using CTE’s would be as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
; WITH cte1 (cat, cnt) AS ( SELECT a.Category, COUNT(*) FROM TableA a ), cte2 (cat, cnt) AS ( SELECT b.Category, COUNT(*) FROM TableB b ) SELECT cte1.cat, cte1.cnt AS CountA, cte2.cnt AS CountB FROM cte1 INNER JOIN cte2 ON cte2.cat = cte1.cat |
My live example was a little more complicated than this but I hope this demonstrates the concept.
Leave a Reply