• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Janeice DelVecchio
Saloon Keeper
Posts: 1809
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: 35279
384
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
Saloon Keeper
Posts: 1809
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic