Bailey Miller Bailey Miller - 26 days ago 8
MySQL Question

MySQL aliased subquery cannot be used in where clause

I have a query given below that returns all the data asked. Then in the next query I need to write I need to restrict the output to where the subquery is greater than 5.

I will show examples but I cannot understand why I cannot do what I am attempting.

Query without restriction

select t1.BOOK_NUM, t1.BOOK_TITLE,
(select count(t2.CHECK_OUT_DATE)
from checkout t2
where t2.BOOK_NUM = t1.BOOK_NUM) as Times_Checked_Out
from book t1
order by Times_Checked_Out desc, t1.BOOK_TITLE;


Screenshot of output
enter image description here

Attempt at query with restrictions

select t1.BOOK_NUM, t1.BOOK_TITLE,
(select count(t2.CHECK_OUT_DATE)
from checkout t2
where t2.BOOK_NUM = t1.BOOK_NUM) as Times_Checked_Out
from book t1
where Times_Checked_Out > 5
order by Times_Checked_Out desc, t1.BOOK_TITLE;


Error
enter image description here

Answer

You can't use a derived column in a where clause, need to use HAVING:

select t1.BOOK_NUM, t1.BOOK_TITLE,
(select count(t2.CHECK_OUT_DATE) 
from checkout t2
where t2.BOOK_NUM = t1.BOOK_NUM) as Times_Checked_Out
from book t1
HAVING Times_Checked_Out > 5
order by Times_Checked_Out desc, t1.BOOK_TITLE;