aspose file tools*
The moose likes JDBC and the fly likes SQL Question -- why can't I use an aggregate function here? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Question -- why can Watch "SQL Question -- why can New topic
Author

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

Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1732
    
  12

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?


When you do things right, people won't be sure you've done anything at all.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31062
    
232

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1732
    
  12

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

Joined: Nov 24, 2005
Posts: 14688
    
  16

Janeice DelVecchio wrote:
Can I use an ORDER BY with the HAVING, too?

Yes.


[My Blog]
All roads lead to JavaRanch
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Question -- why can't I use an aggregate function here?