Efficiently Include Column not in Group By of SQL Query
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.
Did you try adding the field to the group by?
SELECT FkId, COUNT(FkId), a.NameFROM B bINNER JOIN A a ON a.Id=b.FkIdGROUP BY FkId,a.Name
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;