SQL - Filtering And Aggregation

Filtering And Aggregation

Filtering decides which records or groups remain in a result. Aggregation summarizes records with functions such as COUNT, AVG, SUM, MIN, and MAX.

WHERE Vs HAVING

Clause	Filters	Applied	Use case
WHERE	individual records	before grouping	filter raw data
HAVING	grouped records	after grouping	filter aggregated results

Execution order:

WHERE -> GROUP BY -> HAVING

WHERE Example

SELECT title
FROM films
WHERE release_year = 2000;

HAVING Example

SELECT release_year
FROM films
GROUP BY release_year
HAVING AVG(duration) > 120;

Combined Example

SELECT
    release_year,
    AVG(budget) AS avg_budget,
    AVG(gross) AS avg_gross
FROM films
WHERE release_year > 1990
GROUP BY release_year
HAVING AVG(budget) > 60000000
ORDER BY avg_gross DESC
LIMIT 1;

HAVING With NULL

HAVING conditions filter out NULL unless they are explicitly included.

HAVING car_condition != 'c0'

To keep NULL values:

HAVING car_condition IS NULL OR car_condition != 'c0'

Query Execution Order

Understanding SQL execution order helps explain why aliases, filters, and groups behave the way they do.

1. FROM
2. JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. DISTINCT
8. ORDER BY
9. LIMIT/OFFSET

This connects to SQL Joins because joins happen before row filtering and aggregation.