GROUP BY, HAVING and FILTER

Conditional aggregates

Every selected column must be grouped or aggregated. HAVING filters groups after aggregation. The SQL-standard FILTER (WHERE ...) clause (supported by PostgreSQL) computes conditional aggregates such as "hired since 2022" alongside totals in a single scan; elsewhere use sum(CASE WHEN ... THEN 1 ELSE 0 END). count(DISTINCT x) counts unique values. Note that an inner join to departments drops employees without a department.

Summaries that answer questions

GROUP BY, FILTER, ROLLUP and conditional aggregation produce most business reports.

Department metrics with FILTER and HAVING, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Support (one person) is removed by HAVING; Nina has no department and is excluded by the inner join.

SELECT d.name AS dept,
       count(*) AS people,
       round(avg(e.salary)) AS avg_salary,
       count(*) FILTER (WHERE e.hired >= '2022-01-01') AS hired_since_2022,
       count(DISTINCT e.salary) AS distinct_salaries
FROM employees e JOIN departments d ON d.id = e.dept_id
GROUP BY d.name
HAVING count(*) >= 2
ORDER BY people DESC;

Output:

    dept     | people | avg_salary | hired_since_2022 | distinct_salaries 
-------------+--------+------------+------------------+-------------------
 Engineering |      4 |     182500 |                1 |                 3
 Sales       |      3 |     111667 |                2 |                 3
(2 rows)

Round only for presentation

Keep full precision in intermediate steps and round in the final SELECT.

त्वरित जाँच: What does count(*) FILTER (WHERE hired >= '2022-01-01') compute?

• The number of rows in each group hired since 2022
• All rows ignoring the filter
• The rows removed by WHERE
• A boolean