JD Isaacks JD Isaacks - 3 months ago 11
MySQL Question

How to use a temp column in the where clause

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'

Answer

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.