Difference between revisions of "Incorrect GROUP BY"

From SQLZOO
Jump to: navigation, search
 
(2 intermediate revisions by one user 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>
  
<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>
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)
 +
  FROM bbc
 +
  GROUP BY region
 +
</source>
 +
 
<h2>Solutions</h2>
 
<h2>Solutions</h2>
 
*Remove the offending field from the SELECT line
 
*Remove the offending field from the SELECT line
Line 31: Line 36:
 
   FROM bbc
 
   FROM bbc
 
   GROUP BY region
 
   GROUP BY region
</div>
 
 
<source lang=sql class='tidy'></source>
 
<source lang=sql class='setup'></source>
 
 
<source lang='sql' class='def'>SELECT name, region, MAX(population)
 
  FROM bbc
 
  GROUP BY region
 
</source>
 
  
 
<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

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
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense