0

enter image description here

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.

Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
devendande
  • 1
  • aggregated values may be used AFTER group by in the HAVING clause – Paul Maxwell Apr 03 '23 at 05:42
  • cannot reference the alias name "num_of_trips" in `WHERE` because `SELECT` is evaluated after `WHERE` -- one workaround is to repeat the expression used in `SELECT` directly in the `WHERE` clause – Lemonina Apr 03 '23 at 05:43

1 Answers1

0

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.

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51