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?
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;
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.
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
group by job
having Count(a.bossID) > 1;
I would try it with a HAVING clause:
select job "Some job", Count(a.bossID) "Number Of bosses"
from boss a, places b, place_boss ba
where ba.bossid = a.bossid
group by job
having Count(a.bossID)>1
Never mind it should be
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 group by job having Count(a.bossID)>1;
