How to use a temp column in the where clause

Question

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' 

Fouquet

Answer 1

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.

Conjunctiva

@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? – Conjunctiva

Sorry. I've confused MySQL with MS SQL Server. – Serles

Answer 2

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

Alexandriaalexandrian

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

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

Answer 3

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 .

To simplify:

Select Max(Points) as Highest where Highest > 10

won't work, but:

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

will. It's the same in your case.

Unskillful

Answer 4

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, repeating your case statement.

Bergius

Answer 5

If I read your 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.

Paxton