HAVING for filtering groups in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Filtering groups with HAVING clause

Given a table sales with columns region and amount, what is the output of this query?

SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
HAVING SUM(amount) > 1000;

PostgreSQL

CREATE TABLE sales (region TEXT, amount INT);
INSERT INTO sales VALUES
('North', 500), ('North', 600), ('South', 400), ('South', 300), ('East', 1200), ('West', 800);

A [{"region": "North", "total_sales": 1100}]

B [{"region": "North", "total_sales": 1100}, {"region": "East", "total_sales": 1200}]

C [{"region": "East", "total_sales": 1200}, {"region": "West", "total_sales": 800}]

D [{"region": "North", "total_sales": 1100}, {"region": "South", "total_sales": 700}, {"region": "East", "total_sales": 1200}]

💡 Hint

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

The query groups sales by region and sums the amounts. HAVING filters groups where the sum is greater than 1000. 'North' sums to 1100, 'East' sums to 1200, both included. 'South' and 'West' sums are less than or equal to 1000, so excluded.

🧠 Conceptual

intermediate

Understanding HAVING vs WHERE

Which statement correctly explains the difference between WHERE and HAVING clauses in SQL?

A WHERE filters groups after aggregation; HAVING filters groups before aggregation.

B WHERE filters groups after aggregation; HAVING filters rows before grouping.

C WHERE and HAVING both filter rows before grouping but HAVING is faster.

D WHERE filters rows before grouping; HAVING filters groups after aggregation.

💡 Hint

Think about when aggregation happens in the query process.

WHERE filters individual rows before any grouping or aggregation happens. HAVING filters the groups after aggregation functions like SUM or COUNT are applied.

📝 Syntax

advanced

Identify the syntax error in HAVING clause

Which option contains a syntax error in the HAVING clause?

PostgreSQL

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

A HAVING emp_count > 5

B HAVING COUNT(*) > 5

C HAVING COUNT(emp_count) > 5

D HAVING SUM(emp_count) > 5

💡 Hint

Can you use column aliases directly in HAVING in PostgreSQL?

In PostgreSQL, you cannot use column aliases defined in SELECT inside HAVING. You must repeat the aggregate function. Option A uses the alias 'emp_count' which causes a syntax error.

❓ optimization

advanced

Optimizing HAVING with WHERE

Consider this query:

SELECT category, SUM(price) AS total_price
FROM products
WHERE price > 10
GROUP BY category
HAVING SUM(price) > 100;

What is the main benefit of using the WHERE clause here?

A It reduces the number of rows before grouping, improving query performance.

B It allows filtering on aggregated values directly.

C It filters groups after aggregation, so fewer groups are returned.

D It sorts the results before grouping.

💡 Hint

Think about when WHERE filters data compared to HAVING.

WHERE filters rows before grouping and aggregation, so fewer rows are processed in aggregation, improving performance. HAVING filters after aggregation and cannot reduce rows before grouping.

🔧 Debug

expert

Debugging unexpected HAVING results

A developer runs this query:

SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id
HAVING order_count > 3;

But it returns a syntax error. What is the cause?

A GROUP BY must include order_count column.

B COUNT(order_id) cannot be used in HAVING clause.

C Using the alias 'order_count' in HAVING is not allowed in PostgreSQL.

D HAVING clause must come before GROUP BY.

💡 Hint

Check if column aliases can be used in HAVING in PostgreSQL.

PostgreSQL does not allow using SELECT column aliases in HAVING. The aggregate function must be repeated in HAVING clause.