Difference between revisions of "Incorrect GROUP BY"

From SQLZOO
Jump to: navigation, search
(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...")
 
Line 9: Line 9:
  
 
<div>
 
<div>
<h2>Problem</h2><p>
+
<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>

Revision as of 11:47, 19 July 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?

Solutions

  • Remove the offending field from the SELECT line
  • Add the field to the GROUP BY clause
  • Aggregate the offending field
 
 
SELECT name, region, MAX(population)
  FROM bbc
  GROUP BY region
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense