HAVING for filtering groups in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the HAVING clause in SQL?

The HAVING clause is used to filter groups created by GROUP BY based on a condition, similar to how WHERE filters rows before grouping.

beginner

How does HAVING differ from WHERE in SQL queries?

WHERE filters individual rows before grouping, while HAVING filters groups after aggregation is done.

intermediate

Write a simple SQL query using HAVING to find departments with more than 5 employees.

SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) > 5;

intermediate

Can HAVING be used without GROUP BY? Explain.

Yes, HAVING can be used without GROUP BY to filter aggregated results over the entire table, but it is less common.

advanced

Why might you get an error if you use a column in HAVING that is not in GROUP BY or an aggregate function?

Because HAVING filters groups, any column used must be part of the grouping or an aggregate; otherwise, SQL doesn't know how to evaluate it for each group.

What does the HAVING clause filter in an SQL query?

A Groups created by GROUP BY

B Individual rows before grouping

C Only NULL values

D Columns in SELECT clause

HAVING filters groups after aggregation, unlike WHERE which filters rows before grouping.

Which clause is used to filter rows before grouping?

A HAVING

B GROUP BY

C ORDER BY

D WHERE

WHERE filters rows before grouping, HAVING filters groups after aggregation.

Which of these is a valid use of HAVING?

A HAVING name = 'John'

B HAVING salary > 50000

C HAVING COUNT(*) > 10

D HAVING age < 30

HAVING works with aggregate functions like COUNT; conditions on individual columns require WHERE.

Can HAVING be used without GROUP BY?

A Yes, to filter aggregated results over the whole table

B No, it always requires GROUP BY

C Only in MySQL

D Only with ORDER BY

HAVING can filter aggregated results without GROUP BY, though it's less common.

What happens if you use a non-aggregated column in HAVING that is not in GROUP BY?

A Query runs normally

B SQL error occurs

C Column is ignored

D Results are duplicated

SQL requires columns in HAVING to be aggregated or grouped; otherwise, it throws an error.

Explain the difference between WHERE and HAVING clauses in SQL with an example.

Think about when filtering happens in the query process.

Describe a scenario where HAVING is necessary and WHERE cannot be used.

Consider filtering groups by their total or average.