Using Column Alias in WHERE clause in SQL, getting 'Unrecognized Name' Error. What's the workaround?

Question

I have put COUNT(*) AS num_of_trips in the SELECT Clause.

Now, I want to use num_of_trips > 30 in WHERE Clause but it gives "unrecognized name" error.

I also tried to workaround it by putting the same argument in WHERE clause as: ROUND(AVG(tripduration/60),2) > 30 but that also gave error.

Answer 1

Aggregated values may be used after the GROUP BY in the HAVING clause

select column1, count(*) as num_of_trips 
from table1
where column2 = 1 -- NO aggregates available here
group by column1
having count(*) > 30  -- aggregated values are available here

The WHERE clause filters the rows before aggregation; the HAVING clause filters the grouped data and aggregated values may only be referenced in this clause.