Avoid specifying each table field in a GROUP BY clause when using Left Join

Question

I have a simple SQL Server 2008 database with two tables:

TableA: (PK)"ID" "Field" 

and

 TableB: (PK)"ID" (FK)"ID_TableA" "Field" 

I want to highlight all the fields in TableA , as well as the number of corresponding rows in TableB for each row of TableA :

 SELECT A.*, COUNT(B."ID") as "B number" FROM "TableA" A LEFT JOIN "TableB" B ON (A."ID" = B."ID_TableA") GROUP BY A."ID", A."Field" 

This works well, but I have this problem: if TableA further modified (say, we need to add another Field2 column), I must update the SELECT above to include this field in GROUP BY . Otherwise, I get this error when performing the operation:

"The column" TableA.Field2 "is not valid in the selection list because it is not contained in either the aggregate function or the GROUP BY clause"

Is there a way to avoid this, so I can change my TableA without updating all statements like the one above?

Answer 1

You can use this (first Group By in table B, then join table A):

 SELECT A.*, COALESCE("B number", 0) AS "B number" FROM "TableA" A LEFT JOIN ( SELECT B."ID_TableA", COUNT(B."ID") as "B number" FROM "TableB" B GROUP BY B."ID_TableA" ) AS B ON (A."ID" = B."ID_TableA") 

Answer 2

You can use a correlated subquery like this

 SELECT A.* , (SELECT COUNT(*) FROM "TableB" WHERE "ID_TableA" = A."ID") AS "B number" FROM "TableA" A 

Typically, the db engine optimizes them to match (or beat) the performance of the connection.

Answer 3

First, it’s a bad idea to use * so that new columns do not interrupt work. But I think you could do this and get the desired result without specifying columns A:

 SELECT A.*, COUNT(B."ID") OVER (PARTITION BY B."ID_TableA") as "B number" FROM "TableA" A LEFT JOIN "TableB" B ON (A."ID" = B."ID_TableA")