Difference between revisions of "Incorrect GROUP BY"
From SQLZOO
(Created page with "<div class='err'> <div class=params>schema:gisq</div> <div class = "link e-oracle">ORA-00979: not a GROUP BY expression</div> <div class = "link e-mysql">ORA-00979: not a GROU...") |
|||
| (4 intermediate revisions by 2 users not shown) | |||
| Line 8: | Line 8: | ||
<div class = "link e-sqlserver">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)</div> | <div class = "link e-sqlserver">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)</div> | ||
| − | + | <h2>Problem</h2> | |
| − | <h2>Problem</h2 | + | |
<p>When using a GROUP BY clause every field in the SELECT list must be either: </p> | <p>When using a GROUP BY clause every field in the SELECT list must be either: </p> | ||
<ul> | <ul> | ||
| Line 18: | Line 17: | ||
<p>In the example the field <code>name</code> may not be used on the SELECT line.</p> | <p>In the example the field <code>name</code> may not be used on the SELECT line.</p> | ||
<p>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?</p> | <p>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?</p> | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
<source lang='sql' class='def'>SELECT name, region, MAX(population) | <source lang='sql' class='def'>SELECT name, region, MAX(population) | ||
| Line 33: | Line 22: | ||
GROUP BY region | GROUP BY region | ||
</source> | </source> | ||
| + | |||
| + | <h2>Solutions</h2> | ||
| + | *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 | ||
<div class="ecomm e-access" style="display: none"></div> | <div class="ecomm e-access" style="display: none"></div> | ||
</div> | </div> | ||
| + | [[Category:Error]] | ||
Latest revision as of 13:15, 9 August 2012
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
