GROUP BY with WHERE

[johnseito]

Anyone know why this code didn't work, but the code below it works fine?
I have seen WHERE clause works with GROUP BY, just not this one, not sure why.

SELECT seller, iid, startTIME , count(*) as cntBID
FROM bid 
WHERE count(*) = 1
GROUP BY seller, iid, startTIME

SELECT seller, iid, startTIME , count(*) as cntBID
FROM bid 
GROUP BY seller, iid, startTIME
HAVING count(*) = 1

[pbaldy]

The WHERE clause is applied before the aggregating, the HAVING clause after, so that criteria would have to be in the HAVING clause.

[johnseito]

Ok, pbaldy,

Thanks I see what you are saying, yet this code below works.
I believe the below code doesn't rely on GROUP BY bc no aggregation is needed
as oppose to the one with WHERE count(*) =1 is, and GROUP BY has to execute first.

SELECT seller, iid, startTIME , count(*) as cntBID
FROM bid 
WHERE seller = 7
GROUP BY seller, iid, startTIME

[pbaldy]

Because restricting by seller is something you want done before the aggregation. If you wanted to combine the two, you'd still need a HAVING clause for the count = 1. The seller = 7 would actually work in either, but is more efficient as a WHERE.

[johnseito]

Ok, got it. :-)