WHERE vs HAVING

WHERE filters rows before grouping; HAVING filters groups after aggregation. So WHERE cannot use aggregates like count(*), while HAVING usually does.

Logical evaluation order:

1. FROM and JOINs
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT (expressions, window functions)
6. DISTINCT
7. ORDER BY
8. LIMIT / OFFSET

-- Branches with at least 10 final-year students whose average CGPA is above 7.5
select branch, count(*) as students, round(avg(cgpa), 2) as avg_cgpa
from students
where year = 4                      -- row filter: runs first
group by branch
having count(*) >= 10 and avg(cgpa) > 7.5   -- group filter
order by avg_cgpa desc;             -- ORDER BY may use the alias

Put row conditions in WHERE whenever possible: it reduces the rows before the more expensive grouping. The evaluation order also explains why a column alias defined in SELECT cannot be used in WHERE.