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

Question

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'*

Answer 1

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).

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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