Incorrect GROUP BY

schema:gisq

ORA-00979: not a GROUP BY expression

ORA-00979: not a GROUP BY expression

ORA-00979: not a GROUP BY expression

Error 7 ERROR: Attribute xxx.xxx must be GROUPed or used in an aggregate function

SQL0119N An expression starting with "xxx" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803

Msg 8120 Column 'xxx' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (SQL-42000)

Problem

When using a GROUP BY clause every field in the SELECT list must be either:

• One of the GROUP BY terms - in this case region
• An aggregate function - for example SUM or COUNT
• An expression based on the above

In the example the field name may not be used on the SELECT line.

In a group by line each region shows up only once - however in a typical region such as Africa there are several different name values. WHich one should SQL pick?

SELECT name, region, MAX(population)
  FROM bbc
  GROUP BY region

Solutions

• Remove the offending field from the SELECT line

SELECT region, MAX(population)
 FROM bbc
 GROUP BY region

• Add the field to the GROUP BY clause

SELECT name, region, MAX(population)
 FROM bbc
 GROUP BY name,region

• Aggregate the offending field

SELECT MAX(name), region, MAX(population)
 FROM bbc
 GROUP BY region