SQL GROUP BY and HAVING | Data Bucketing and Aggregation

# CHAPTER 13

GROUP BY and HAVING Clauses #

1. Introduction #

2. Learning Objectives #

• Successfully mix raw columns and aggregate functions.

• Understand how GROUP BY buckets data.

• Perform grouped mathematical calculations.

• Filter aggregated results using the HAVING clause.

• Differentiate between the functionality of WHERE and HAVING.

3. The GROUP BY Clause #

-- Step 1: Tell SQL you want the Department Name AND the Total Salary
SELECT department, SUM(salary) AS 'Total Payroll'
FROM employees
-- Step 2: Tell SQL how to squish the data to prevent a crash!
GROUP BY department;

*(Result: Instead of a crash, SQL creates 3 buckets (HR, IT, Sales), adds the salaries inside each bucket, and returns exactly 3 perfect rows!)*

4. Grouping by Multiple Columns #

SELECT region, year, SUM(revenue) AS 'Total Sales'
FROM regional_sales
GROUP BY region, year;

*(Result: It generates unique buckets for "North-2022", "North-2023", "South-2022", etc.)*

5. Filtering Aggregated Data (The HAVING Clause) #

You might try to write this: SELECT department, SUM(salary) FROM employees WHERE SUM(salary) > 1000000 GROUP BY department; CRASH! The WHERE clause filters individual rows *before* the math happens. You cannot use WHERE on an aggregate function!

To filter data *after* the math has been calculated, you must use the HAVING clause.

SELECT department, SUM(salary) AS 'Total Payroll'
FROM employees
GROUP BY department
-- HAVING filters the buckets AFTER the math is done!
HAVING SUM(salary) > 1000000;

6. The Complete Order of Execution #

1. 1. FROM: Gather the tables.

1. 2. WHERE: Filter the raw, individual rows.

1. 3. GROUP BY: Bucket the remaining rows.

1. 4. HAVING: Filter the aggregated buckets.

1. 5. SELECT: Format the output columns.

1. 6. ORDER BY: Sort the final result.

1. 7. LIMIT: Cut off the output grid.

7. Mini Project: The Marketing Report #

SELECT country, COUNT(*) AS 'Total Users'
FROM users
WHERE status = 'Active'           -- 1. Only look at active people
GROUP BY country                  -- 2. Bucket them by their country
HAVING COUNT(*) > 500             -- 3. Throw away buckets with < 500 people
ORDER BY COUNT(*) DESC;           -- 4. Sort the big buckets at the top

8. Common Mistakes #

• The "Select List" Rule Violation: The most common error in all of SQL! If you put a raw column in your SELECT statement (e.g., SELECT state, city, COUNT(*)), you MUST include *all* raw columns in your GROUP BY.

9. Best Practices #

• Alias the Math, but group by the Raw Formula: In some database engines (like strict versions of PostgreSQL), you cannot use your SELECT aliases inside the HAVING clause. It is safer to rewrite the math.

10. Exercises #

1. 1. What clause is strictly required if you want to mix a raw dimension column with an aggregate function in your SELECT statement?

1. 2. What is the explicit architectural difference between the WHERE clause and the HAVING clause?

11. SQL Challenges #

SELECT category, AVG(price) AS 'Average Price'
FROM products
GROUP BY category
HAVING AVG(price) > 50;

12. MCQ Quiz with Answers #

13. Interview Questions #

• Q: Detail the chronological Execution Order of a complex SQL query containing SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. Explain why the database engine processes them in that specific order.

• Q: A junior developer writes a query selecting 4 different categorical columns and 1 aggregate function, but only groups by 1 column. Explain the "Select List" rule violation and why the database engine physically cannot process the request.

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #