Difference between revisions of "Invalid Group function"

From SQLZOO
Jump to: navigation, search
(Created page with "<div class='err'> <div class=params>schema:gisq</div> <div class = "link e-oracle">ORA-00934: group function is not allowed here</div> <div class = "link e-mysql">Error 1111 I...")
 
 
(One intermediate revision by one user not shown)
Line 7: Line 7:
 
<div class = "link e-postgres">ERROR: Aggregates not allowed in WHERE clause</div>
 
<div class = "link e-postgres">ERROR: Aggregates not allowed in WHERE clause</div>
 
<div class = "link e-db2">SQL0120N A WHERE clause, GROUP BY clause, SET clause, or SET transition-variable statement contains a column function.</div>
 
<div class = "link e-db2">SQL0120N A WHERE clause, GROUP BY clause, SET clause, or SET transition-variable statement contains a column function.</div>
<div class = "link e-sqlserver>Msg 1013,
+
<div class = "link e-sqlserver">Msg 1013,
 
Ambiguous column name 'name'.</div>
 
Ambiguous column name 'name'.</div>
  
Line 37: Line 37:
 
</div>
 
</div>
 
</div>
 
</div>
 +
[[Category:Error]]

Latest revision as of 14:20, 9 August 2012

schema:gisq

Problem

We want to filter the results returned based on an aggregate function such as COUNT or SUM.

The WHERE clause may not be used for this. The WHERE conditions are considered before the aggregation.

Solutions

  • Put the condition into the HAVING clause, after the GROUP BY clause.
  • SELECT winner FROM nobel
      GROUP BY winner
      HAVING COUNT(winner)>1
    
 
 
SELECT winner FROM nobel
 WHERE COUNT(winner)>1
GROUP BY winner
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense