Alex Rivera | Logout

How to use a temp column in the where clause

Asked 2009-03-10T15:37:45.300
10

Why can't I use a temporary 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 two 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.

All this makes sense to me, except 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

which gives me this error:

#1054 - Unknown column 'brand_count' in 'where clause' 
Edit
Report

1 Answer

3

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

answered 2009-03-10T15:40:50.597

Your Answer