SQL Error unknown column in where clause?

Question

Write a query to display hotel id, hotel name, and number of orders taken by hotels that have taken orders more than 5 times. Give an alias name for number of orders as 'NO_OF_ORDERS'.sort the result based on hotel id in ascending order.

Data Base Image

The Query I wrote:

select hotel_details.hotel_id, hotel_name, count(orders.hotel_id) as no_of_orders 
from hotel_details join orders on hotel_details.hotel_id = orders.hotel_id
where no_of_orders > 5
group by orders.hotel_id order by no_of_orders; 

Error I go:

unkown column 'number_of_orders' in 'where clause'

Answer 1

You need a HAVING clause, not a WHERE clause:

select
    hd.hotel_id,
    hd.hotel_name,
    count(o.hotel_id) as no_of_orders 
from hotel_details hd
inner join orders o on hd.hotel_id = o.hotel_id
group by
    o.hotel_id
having
    no_of_orders > 5
order by
    no_of_orders;

The count of orders occurs during the GROUP BY portion of the query, at which point WHERE has already happened. So, assertions on anything from GROUP BY belong in a HAVING clause.