Problem with SQL select statment

Question

Ok I can't figure this one out I have

select job "Some job", Count(a.bossID) "Number Of bosses" 
from boss a, places b, place_boss ba 
where ba.bossid = a.bossid and ba.placeid = b.placeid 
   and Count(a.bossID)>1 
group by job;

I get Group function not allowed here I need make sure that if the job has one boss it doesn't show.

Any Idea what I'm messing up here?

Answer 1

You need to use having. The Where clause cannot contain aggregates. Additionally I have changed it to use the explicit JOIN syntax as this is (IMO) clearer and less likely to cause inadvertent Cartesian joins.

select job             "Some job",
       Count(a.bossID) "Number Of bosses"
from   boss a
       join place_boss ba
         on ba.bossid = a.bossid
       join places b
         on ba.placeid = b.placeid
group  by job
having Count(a.bossID) > 1;  

Answer 2

where clauses are applied at the row level, literally a "should this row be included in the result set". At the time that decision is being made, it's impossible for the count() function to have finished its work, as not all rows have been counted yet.

That's where having clauses come in to play. They're exactly like where clauses, but applied immediately before the result set gets sent to the client, after the aggregate functions have completed their work.

Technically, doing

SELECT * FROM table HAVING somefield='a'
SELECT * FROM table WHERE somefield='a'

are identical, except the having clause will entail a bit more work on the part of the server because all the rows are fetched, THEN filtered, rather than being filtered before fetching.