CODEWITHSUNDEEP
sql
Jeff
Jeff

Reputation: 480

Problem with SQL select statment

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?

Upvotes: 2

Views: 228

Answers (5)

Marc B
Marc B

Reputation: 360692

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.

Upvotes: 3

Jeff
Jeff

Reputation: 480

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;

Upvotes: 0

duffymo
duffymo

Reputation: 308763

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

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300559

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;

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453287

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;

Upvotes: 7

Related Questions