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' 

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

Solution 2

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

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

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

View more solutions

Comments

[TheTXI]

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

[Hawk Kroeger]

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

[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?

[thorn0]

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