Unknown column when using subquery result column in WHERE clause

Question

I have a few tables I'm joining to select a column for totals using COUNT(). This query works great with returning the total column:

SELECT *,`excel`.`drivers`.`AutoNumber` AS `current_driver`,
(SELECT COUNT(`issues`.`categories`.`points`) FROM `issues`.`points` INNER JOIN `excel`.`drivers` ON `issues`.`points`.`driver_id` = `excel`.`drivers`.`AutoNumber` INNER JOIN `issues`.`categories` ON `issues`.`categories`.`id` = `issues`.`points`.`category_id` WHERE `excel`.`drivers`.`AutoNumber`=`current_driver`) AS `total` 
FROM `issues`.`points` 
INNER JOIN `excel`.`drivers` ON `issues`.`points`.`driver_id` = `excel`.`drivers`.`AutoNumber` 
INNER JOIN `issues`.`categories` ON `issues`.`categories`.`id` = `issues`.`points`.`category_id`

But now I'm trying to create a query that compares total in there WHERE clause, like this:

SELECT *,`excel`.`drivers`.`AutoNumber` AS `current_driver`,
(SELECT COUNT(`issues`.`categories`.`points`) FROM `issues`.`points` INNER JOIN `excel`.`drivers` ON `issues`.`points`.`driver_id` = `excel`.`drivers`.`AutoNumber` INNER JOIN `issues`.`categories` ON `issues`.`categories`.`id` = `issues`.`points`.`category_id` WHERE `excel`.`drivers`.`AutoNumber`=`current_driver`) AS `total` 
FROM `issues`.`points` 
INNER JOIN `excel`.`drivers` ON `issues`.`points`.`driver_id` = `excel`.`drivers`.`AutoNumber` 
INNER JOIN `issues`.`categories` ON `issues`.`categories`.`id` = `issues`.`points`.`category_id` WHERE `total` = 3

This returns with an error that total is an unknown column. Any ideas?

Answer 1

Column aliases cannot be used in WHERE clause. Try

select a.* 
FROM 
(
SELECT [column_list]
,`excel`.`drivers`.`AutoNumber` AS `current_driver`,
(SELECT COUNT(`issues`.`categories`.`points`) FROM `issues`.`points` INNER JOIN `excel`.`drivers` ON `issues`.`points`.`driver_id` = `excel`.`drivers`.`AutoNumber` INNER JOIN `issues`.`categories` ON `issues`.`categories`.`id` = `issues`.`points`.`category_id` WHERE `excel`.`drivers`.`AutoNumber`=`current_driver`) AS `total` 
FROM `issues`.`points` 
INNER JOIN `excel`.`drivers` ON `issues`.`points`.`driver_id` = `excel`.`drivers`.`AutoNumber` 
INNER JOIN `issues`.`categories` ON `issues`.`categories`.`id` = `issues`.`points`.`category_id`
)a 
WHERE a.`total` >3

You can also repeat the whole definition of "total" in WHERE - smart optimizer will very likely generate the same plan, but I wouldn't do that for readability sake.