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.

Practice

1. What is the main purpose of the HAVING clause in a PostgreSQL query?

easy

A. To join two tables

B. To filter rows before grouping

C. To sort the result set

D. To filter groups after aggregation

Solution

1. Step 1: Understand the role of HAVING

   The HAVING clause is used to filter groups created by GROUP BY based on aggregate conditions.

2. Step 2: Differentiate HAVING from WHERE

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

3. Final Answer:

   To filter groups after aggregation -> Option D

4. Quick Check:

   HAVING filters groups = D [OK]

Hint: Remember HAVING filters groups, WHERE filters rows [OK]

Common Mistakes:

• Confusing HAVING with WHERE clause
• Thinking HAVING sorts data
• Using HAVING without GROUP BY

2. Which of the following is the correct syntax to filter groups having a total sales greater than 1000 using HAVING?

easy

A. SELECT store, SUM(sales) FROM sales_data HAVING SUM(sales) > 1000 GROUP BY store;

B. SELECT store, SUM(sales) FROM sales_data GROUP BY store HAVING SUM(sales) > 1000;

C. SELECT store, SUM(sales) FROM sales_data WHERE SUM(sales) > 1000 GROUP BY store;

D. SELECT store, SUM(sales) FROM sales_data GROUP BY store WHERE SUM(sales) > 1000;

Solution

1. Step 1: Identify correct order of clauses

   GROUP BY must come before HAVING in SQL syntax.

2. Step 2: Check aggregate usage in HAVING

   HAVING uses aggregate functions like SUM(sales) to filter groups.

3. Final Answer:

   SELECT store, SUM(sales) FROM sales_data GROUP BY store HAVING SUM(sales) > 1000; -> Option B

4. Quick Check:

   GROUP BY before HAVING = B [OK]

Hint: HAVING always follows GROUP BY clause [OK]

Common Mistakes:

• Placing HAVING before GROUP BY
• Using WHERE with aggregate functions
• Incorrect clause order

3. Given the table orders with columns customer_id and amount, what will this query return?

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) > 2;

medium

A. All customers with more than 2 orders

B. All customers with exactly 2 orders

C. All customers with less than 2 orders

D. Syntax error due to HAVING clause

Solution

1. Step 1: Understand the GROUP BY and COUNT(*)

   The query groups rows by customer_id and counts orders per customer.

2. Step 2: Apply HAVING filter

   HAVING COUNT(*) > 2 keeps only customers with more than 2 orders.

3. Final Answer:

   All customers with more than 2 orders -> Option A

4. Quick Check:

   HAVING COUNT(*) > 2 filters groups with >2 rows [OK]

Hint: HAVING filters groups by aggregate results [OK]

Common Mistakes:

• Confusing > 2 with = 2
• Thinking HAVING causes syntax error
• Ignoring GROUP BY effect

4. Identify the error in this query:

SELECT department, AVG(salary) FROM employees HAVING AVG(salary) > 50000 GROUP BY department;

medium

A. HAVING clause used before GROUP BY

B. AVG() cannot be used in HAVING

C. Missing WHERE clause before HAVING

D. No error, query is correct

Solution

1. Step 1: Check clause order

   SQL requires GROUP BY before HAVING clause.

2. Step 2: Identify error in query

   HAVING appears before GROUP BY, which is invalid syntax.

3. Final Answer:

   HAVING clause used before GROUP BY -> Option A

4. Quick Check:

   GROUP BY must precede HAVING [OK]

Hint: GROUP BY comes before HAVING always [OK]

Common Mistakes:

• Placing HAVING before GROUP BY
• Assuming AVG() not allowed in HAVING
• Confusing WHERE and HAVING

5. You have a sales table with columns region, salesperson, and amount. Write a query to find regions where the total sales exceed 10,000 but only include salespeople who individually sold more than 3,000. Which query correctly applies HAVING to achieve this?

hard

A. SELECT region, salesperson, SUM(amount) FROM sales GROUP BY region, salesperson HAVING SUM(amount) > 3000 AND SUM(amount) > 10000;

B. SELECT region, SUM(amount) FROM sales GROUP BY region HAVING SUM(amount) > 10000 AND salesperson IN (SELECT salesperson FROM sales GROUP BY salesperson HAVING SUM(amount) > 3000);

C. SELECT region, salesperson, SUM(amount) FROM sales GROUP BY region, salesperson HAVING SUM(amount) > 3000 AND region IN (SELECT region FROM sales GROUP BY region HAVING SUM(amount) > 10000);

D. SELECT region, salesperson, SUM(amount) FROM sales WHERE SUM(amount) > 3000 GROUP BY region, salesperson HAVING SUM(amount) > 10000;

Solution

1. Step 1: Understand filtering conditions

   We want regions with total sales > 10,000 and salespeople with sales > 3,000.

2. Step 2: Analyze correct logic

   Group by region and salesperson, filter salespeople with >3000 sales using HAVING SUM(amount) > 3000, and filter regions with total sales > 10000 using a subquery in HAVING: region IN (SELECT region FROM sales GROUP BY region HAVING SUM(amount) > 10000).

3. Step 3: Check HAVING usage

   This correctly applies HAVING on both salesperson and region aggregates using subquery.

4. Final Answer:

   SELECT region, salesperson, SUM(amount) FROM sales GROUP BY region, salesperson HAVING SUM(amount) > 3000 AND region IN (SELECT region FROM sales GROUP BY region HAVING SUM(amount) > 10000); -> Option C

5. Quick Check:

   Use HAVING with subqueries for multi-level group filters [OK]

Hint: Use HAVING with subqueries for multi-level group filters [OK]

Common Mistakes:

• Using WHERE with aggregate functions
• Incorrect clause order
• Combining unrelated HAVING conditions without subqueries