why cant I use a temp column in the where clause?

for example this query:

Select 
    product_brand, 
    (CASE WHEN COUNT(product_brand)>50 THEN 1 ELSE 0 END) AS brand_count 
FROM 
    products 
WHERE 
    1 
GROUP BY 
    product_brand

This brings up 2 columns one called product_brand and one called brand_count, brand_count is created on the fly and is always 1 or 0 depending on whether or not there are 50 or products with that brand.

OK all this makes sense to me....but what does not make sense to me is that I can't select only if brand_count = 1 as in this query below:

Select 
    product_brand, 
   (CASE WHEN COUNT(product_brand)>50 THEN 1 ELSE 0 END) AS brand_count 
FROM 
    products 
WHERE 
   brand_count = 1 
GROUP BY 
   product_brand

gives me this error: *#1054 - Unknown column 'brand_count' in 'where clause'*

From stackoverflow

• Because it has no idea what that column is until after it's done the processing. If you want to access the column by that name you would have to use a subquery. Otherwise you are going to have to qualify the column without the name you gave it (i.e. repeating your case statement).

• Use HAVING instead.

  Select
      product_brand,
      (CASE WHEN COUNT(product_brand)>50 THEN 1 ELSE 0 END) AS brand_count
    FROM products
    GROUP BY product_brand
    HAVING brand_count = 1
  

  WHERE is evaluated before the GROUP BY. HAVING is evaluated after.

  thorn : You're wrong. It doesn't work in HAVING too.

  derobert : @thorn: its always worked for me in MySQL. Maybe there is something else wrong in your query? Which version of MySQL are you running, and do you have one of the strict options enabled?

  thorn : Sorry. I've confused MySQL with MS SQL Server.

• Because in SQL the columns are first "selected" and then "projected".

  TheTXI : That's much more concise than my attempt at explaining :)

  zodeus : Thanks TheTXI :P, hurray for DB design courses they finally paid off.

• You have to use the full clause so you will need

  Select product_brand, (CASE WHEN COUNT(product_brand)>50 THEN 1 ELSE 0 END) AS  
  brand_count FROM products WHERE (CASE WHEN COUNT(product_brand)>50 THEN 1 ELSE 0 END) 
  = 1 GROUP BY product_brand
  

  This is the same for any calculated field in any sql statement

  eg to simplify

  Select Max(Points) as Highest where Highest > 10
  

  wont work, but

  Select Max(Points) as Highest where Max(Points) > 10
  

  will. Its the same in your case.

• If I read you intent correctly you can re-write this query to read:

  Select product_brand, COUNT(product_brand) AS brand_count FROM products GROUP BY product_brand HAVING COUNT(product_brand) > 50

  This will give you all product_brands that have a count > 50 and will also show you the count for each.

  -Tom