Effective inclusion of a column not in a group by SQL queries

Question

Considering

Table a

Id INTEGER Name VARCHAR(50) 

Table B

 Id INTEGER FkId INTEGER ; Foreign key to Table A 

I want to count the values ​​of each FkId value:

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

Now I just 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 not contained in the GROUP BY (it throws an error is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause ).

The goal is to move from a conclusion like this

 FkId Count 1 42 2 25 

for output as follows

 FkId Count Name 1 42 Ronald 2 22 John 

There are many coincidences for SO for this error message, but some, for example, https://stackoverflow.com/a/312960/ ... have comments such as “will generate 3 scan tables, not 1, so it will not scale.”

How can I effectively include a field from a merged Table B (which has a 1: 1 FkId 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 Table B , and then attach this result (one row per FkId ) to Table A and take a few more columns from Table A into your final result.

Answer 2

Did you try to add a field to the group?

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

Answer 3

 select t3.Name, t3.FkId, t3.countedFkId from (a t1 join (select t2.FkId, count(FkId) as countedFkId from b t2 group by t2.FkId) on t1.Id = t2.FkId) t3;