AI Reading
Quick summary of this article
Chapter 7: SQL Aggregate Functions & GROUP BY / HAVING
AI Reading
Quick summary of Chapter 7
Data aggregation transforms raw transaction records into business intelligence. In this chapter, you will master the five core aggregate functions, group data with GROUP BY, and understand the critical execution differences between WHERE and HAVING.
- Aggregate Functions: Calculate summary metrics with MIN, MAX, SUM, AVG, and COUNT.
- COUNT Varieties: Distinguish COUNT(*) from COUNT(column) and COUNT(DISTINCT column).
- Data Grouping: Bucket rows into summarized categories using the GROUP BY clause.
- HAVING vs WHERE: Master the query execution lifecycle to know when to filter raw rows vs aggregated groups.
- SQL Mode Strictness: Understand ONLY_FULL_GROUP_BY and avoid invalid column references.
Welcome to Chapter 7. In real-world applications, businesses need aggregated summaries: monthly revenue totals, department headcounts, average employee salaries, and peak sales metrics. In SQL, this is accomplished through Aggregate Functions combined with the GROUP BY and HAVING clauses.
In this chapter, we will study the mathematical functions available in MySQL, understand how grouping works, and learn why confusing WHERE with HAVING is one of the most common mistakes made by SQL developers.
📊 7.1 The 5 Standard SQL Aggregate Functions
| Aggregate Function | Description | Null Handling Behavior |
|---|---|---|
COUNT(*) |
Counts all rows matching the criteria | Includes rows containing NULLs |
COUNT(column) |
Counts rows where the column is NOT NULL | Ignores NULL values in that column |
SUM(column) |
Calculates the total mathematical sum | Ignores NULL values |
AVG(column) |
Calculates the mathematical mean | Ignores NULL values (divides by count of non-nulls) |
MIN(column) |
Returns the lowest value | Ignores NULL values |
MAX(column) |
Returns the highest value | Ignores NULL values |
-- Calculating company-wide payroll statistics
SELECT
MIN(salary) AS min_sal,
MAX(salary) AS max_sal,
ROUND(AVG(salary)) AS avg_sal,
SUM(salary) AS total_payroll,
COUNT(*) AS total_employees
FROM employees;👥 7.2 Grouping Data with GROUP BY
The GROUP BY clause groups all rows that share identical values into summary rows:
-- Count how many employees were hired in each year
SELECT
YEAR(hire_date) AS hired_year,
COUNT(*) AS employees_hired_count
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY employees_hired_count DESC;⚖ 7.3 Filtering Groups: WHERE vs HAVING
⚠ The Golden Rule of SQL Execution:
WHEREfilters individual rows before aggregation takes place.HAVINGfilters aggregated groups after theGROUP BYcalculation completes.
-- WRONG: WHERE cannot filter an aggregate function!
-- SELECT department_id, COUNT(*) FROM employees WHERE COUNT(*) > 3 GROUP BY department_id;
-- CORRECT: Using HAVING to filter aggregate groups:
SELECT
department_id,
COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 3
ORDER BY emp_count DESC;⚠ 7.4 The Whitespace Trap & ONLY_FULL_GROUP_BY
- Whitespace Error (Error 1630): In MySQL/MariaDB, never put a space between an aggregate function and its opening parenthesis (e.g.
COUNT (col)). UnlessIGNORE_SPACESQL mode is enabled, MySQL will fail with:FUNCTION count does not exist. Always writeCOUNT(col). - ONLY_FULL_GROUP_BY (Error 1055): Any column in your
SELECTlist that is not wrapped in an aggregate function must appear in yourGROUP BYclause. Selecting raw unaggregated columns along with a group is non-deterministic and blocked in modern MySQL.
❓ Frequently Asked Questions (FAQ)
1. Can I use column aliases in the HAVING clause?
Yes! Because HAVING executes after the SELECT clause assigns column aliases, you can write HAVING emp_count > 3.
2. How does AVG() handle NULL values?
AVG() ignores NULL values completely. For example, if salaries are 1000, 2000, and NULL, AVG() returns (1000 + 2000) / 2 = 1500, not 3000 / 3 = 1000.