help with GROUP BY

[schilly]

Here is one query I have that works:

SELECT company,

  retail_vendors.id as retail_id,

  COUNT(retail_codes.id) as coupons_assigned,

  SUM(IF(redeemed=1,1,0)) as coupons_used,

  SUM(IF(shipped=1,1,0)) as shipped,

  SUM(IF(redeemed=0,1,0)) as coupons_left

FROM retail_vendors LEFT JOIN retail_codes ON retail_vendors.id = vendor

WHERE status = 1

GROUP BY retail_id ORDER BY company

 

Now I want to select vendors with <= 5 coupons left. I'm trying this:

SELECT company, retail_vendors.id as retail_id,

SUM(IF(redeemed=0,1,0)) as coupons_left

FROM retail_vendors LEFT JOIN retail_codes ON retail_vendors.id = vendor

WHERE SUM(IF(redeemed=0,1,0)) <= 5 GROUP BY retail_vendors.id ORDER BY company

 

but I keep getting an error: "invalid use of group function". What's the issue here or is there a better way to do this? I tried WHERE coupons_left <= 5 and that didn't work either.

[PFMaBiSmAd]

Add the following in between the GROUP BY and the ORDER BY -

HAVING coupons_left <= 5

[schilly]

ah genius!!!

 

I've never used the HAVING clause before. Never quite knew how it was used.