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