GROUP BY and HAVING Clauses

# CHAPTER 11

GROUP BY and HAVING Clauses #

1. Introduction #

2. Learning Objectives #

• Resolve aggregate syntax errors using the GROUP BY clause.

• Group data by a single column to generate statistical reports.

• Group data by multiple columns for deep analytics.

• Understand why the WHERE clause cannot filter aggregate data.

• Use the HAVING clause to filter GROUP BY results.

3. The GROUP BY Clause #

Let's solve the problem from the last chapter: We want a list of departments, and the total headcount of each department.

SELECT department, COUNT(*) AS "Total Employees"
FROM employees
GROUP BY department;

How it works:

1. 1. PostgreSQL grabs the 1,000 employees.

1. 2. It groups them by the department string (e.g., it puts all the 'IT' rows in one bucket, all the 'HR' rows in another).

1. 3. It runs COUNT(*) on each individual bucket.

1. 4. *Output: 4 rows (IT: 500, HR: 50, Sales: 450).*

4. Grouping by Multiple Columns #

SELECT department, job_title, AVG(salary) AS "Avg Salary"
FROM employees
GROUP BY department, job_title
ORDER BY department ASC;

5. The Limitation of the WHERE Clause #

*Instinct says:* SELECT dept, COUNT(*) FROM employees WHERE COUNT(*) > 100 GROUP BY dept; THIS WILL CRASH!

Why? The WHERE clause runs *before* the GROUP BY happens. PostgreSQL cannot filter on a COUNT that hasn't been mathematically calculated yet! The WHERE clause strictly filters raw, individual rows.

6. The HAVING Clause #

-- Find departments with massive payrolls
SELECT department, SUM(salary) AS "Total Payroll"
FROM employees
GROUP BY department
HAVING SUM(salary) > 1000000;

7. The Ultimate Query Structure #

1. 1. SELECT (What columns/math to return)

1. 2. FROM (Which table)

1. 3. WHERE (Filter raw rows before math)

1. 4. GROUP BY (Sort rows into buckets)

1. 5. HAVING (Filter the buckets after math)

1. 6. ORDER BY (Sort the final output)

1. 7. LIMIT (Restrict final output)

8. Mini Project: E-commerce Reporting #

SELECT category, SUM(price * quantity) AS total_revenue
FROM orders
WHERE order_date >= '2023-01-01' -- Only look at this year (Filters Raw Rows)
GROUP BY category                -- Bucket by Category
HAVING SUM(price * quantity) > 50000 -- Filter out the poor performing buckets
ORDER BY total_revenue DESC;     -- Sort highest to lowest

9. Common Mistakes #

• Forgetting columns in the GROUP BY: If you SELECT columnA, columnB, SUM(price), you must have GROUP BY columnA, columnB. Any column in the SELECT that is NOT wrapped in an aggregate function MUST exist in the GROUP BY clause.

• Using WHERE instead of HAVING: Trying to use WHERE SUM(x) > 10 is the most common beginner error. Always remember: WHERE is for rows, HAVING is for groups.

10. Best Practices #

• Filter Early with WHERE: If you want to find the total sales of 'Electronics', do not GROUP BY everything, calculate all the math, and then use HAVING category = 'Electronics'. It is a massive waste of CPU. Use WHERE category = 'Electronics' *before* the group to discard irrelevant data immediately.

11. Exercises #

1. 1. Write a query to find the total number of orders placed by each user_id.

1. 2. Modify the previous query to only show users who have placed *more than 5* total orders.

12. SQL Challenges #

SELECT category, MIN(price) AS lowest_price
FROM products
GROUP BY category
HAVING MIN(price) > 100;

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the exact execution order of a SQL query containing SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.

• Q: If you want to calculate the total revenue of the 'Toys' category, would you filter for 'Toys' using the WHERE clause or the HAVING clause? Why (from a performance perspective)?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #