Win a copy of Android Programming: The Big Nerd Ranch Guide this week in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

SQL Question -- why can't I use an aggregate function here?  RSS feed

 
Janeice DelVecchio
Bartender
Posts: 1810
12
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This, to me, looks like it should return the department_id and AVG(salary) where the AVG(salary) is greater than $8000..... but the tutorial says it returns an error....

Why?

from Skillsoft SQL course - creating and manipulating group functions

Okay, so if this is synctactically incorrect.... how SHOULD it look? Do I need a subquery instead?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 36875
481
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Janeice,
Aggregate functions can't go in the where clause because that goes against each row rather than the grouped function. Instead it goes in the "having" clause.
 
Janeice DelVecchio
Bartender
Posts: 1810
12
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, Jeanne! The book had something complicated to say about the order the statement gets processed. Your answer makes a ton more sense.

So I would do this:



Or this (I think) would be ok too:


Great!

Can I use an ORDER BY with the HAVING, too?
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Janeice DelVecchio wrote:
Can I use an ORDER BY with the HAVING, too?

Yes.
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!