GROUP BY

• inevercheckthis2002

  SSCommitted

  Points: 1819

  More actions

  April 3, 2014 at 12:43 pm

  #287309

  I frequently get tied up with GROUP BY errors, and I'm looking for a straight-forward explanation on how it works - how to avoid these errors.

  The first error I encounter goes something like this:

  SELECT A, B , C

  FROM MyTable

  where C > 2 and C < 98

  GROUP BY A

  Error:

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

  So, I modify as follows:

  SELECT A, B , C

  FROM MyTable

  where C > 2 and C < 98

  GROUP BY A, B

  Error:

  Each GROUP BY expression must contain at least one column that is not an outer reference.

  ??

  Any advice about GROUP BY would be greatly appreciated.

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  April 3, 2014 at 12:53 pm

  #1703038

  Think of the columns in the group by as buckets, any aggregation is then applied to each bucket;

  ColAColB

  A1

  B2

  A2

  B3

  Group by ColA -> Aggregation on B

  A (1,2)

  B (2,3)

  or in other words, it defines the level of detail, in this case, the distinct values in ColA

  😎