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

Question

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

TableA: 
    (PK)"ID"
    "Field"

and

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

I want to select all the fields in TableA and also how many corresponding rows in TableB there are for every TableA row:

SELECT A.*, 
       COUNT(B."ID") as "B's 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 issue: if TableA is further modified (let's say we have to add another Field2 column) I have to update the SELECT statement above to include that field in the GROUP BY clause. Otherwise I receive this error when executing the operation:

"Column 'TableA.Field2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

Is there a way to avoid this so I can modify my TableA without also updating all the statement like the one above?

Since the presence of a new column in the result set from this query will upset any consuming code (or be ignored by such code) anyway, why not follow best practices, avoid `select *` (except within `EXISTS`), and just specify the columns that this query should return? (If it's ignored by the consuming code, then you're making SQL Server do more work than it needs to, and may force inefficient plans to be used for no benefit) — Damien_The_Unbeliever, Mar 10 '12 at 16:01

score 5 · Accepted Answer · answered Mar 10 '12 at 11:22

You could use this (first Group By in table B, then Join to table A):

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

score 1 · Answer 2 · answered Mar 10 '12 at 13:46

1

You can use a correlated subquery like this

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

Usually db engine optimizes these to match (or beat) join performance.

answered Mar 10 '12 at 13:46

wqw

11,771
1
33
41

score 1 · Answer 3 · answered Mar 10 '12 at 19:35

1

First, it's a bad idea to be using * to allow new columns not to break things. But I think you could do this and get the result you want without specifying the columns of A:

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

answered Mar 10 '12 at 19:35

Steve Kass

7,144
20
26

This will return multiple times each row in `TableA`. – wqw Mar 11 '12 at 20:37

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

3 Answers3