sql server - Efficiently Include Column not in Group By of SQL Query

Question

Given

Table A

Id   INTEGER
Name VARCHAR(50)

Table B

Id   INTEGER
FkId INTEGER  ; Foreign key to Table A

I wish to count the occurrances of each FkId value:

SELECT FkId, COUNT(FkId) 
FROM B 
GROUP BY FkId

Now I simply want to also output the Name from Table A.

This will not work:

SELECT FkId, COUNT(FkId), a.Name
FROM B b
INNER JOIN A a ON a.Id=b.FkId
GROUP BY FkId

because a.Name is not contained in the GROUP BY clause (produces is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause error).

The point is to move from output like this

FkId  Count
1      42
2      25

to output like this

FkId  Count  Name
1      42     Ronald
2      22     John

There are quite a few matches on SO for that error message, but some e.g. https://stackoverflow.com/a/6456944/141172 have comments like "will generate 3 scans on the table, rather than 1, so won't scale".

How can I efficiently include a field from the joined Table B (which has a 1:1 relationship to FkId) in the query output?

Answer 1

You can try something like this:

   ;WITH GroupedData AS
   (
       SELECT FkId, COUNT(FkId) As FkCount
       FROM B 
       GROUP BY FkId
   ) 
   SELECT gd.*, a.Name
   FROM GroupedData gd
   INNER JOIN dbo.A ON gd.FkId = A.FkId

Create a CTE (Common Table Expression) to handle the grouping/counting on your Table B, and then join that result (one row per FkId) to Table A and grab some more columns from Table A into your final result set.