Grouping and Aggregation

Aggregation functions perform a calculation on a set of rows and return a single row representing the result. They are crucial for generating reports, analytics, and summaries.

Aggregate Functions

The most common aggregate functions are:

• COUNT(): Returns the number of rows.
• SUM(): Returns the total sum of a numeric column.
• AVG(): Returns the average value of a numeric column.
• MIN(): Returns the smallest value.
• MAX(): Returns the largest value.

-- Find the total number of users
SELECT COUNT(id) FROM users;

-- Find the highest salary
SELECT MAX(salary) FROM employees;

GROUP BY

Usually, you don't want to aggregate the entire table into a single result. You want to aggregate data per category. This is where GROUP BY comes in. It groups rows that have the same values into summary rows.

SELECT 
    department, 
    COUNT(id) AS employee_count,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department;

(This returns one row for each department, alongside the calculated aggregates for that department).

The Golden Rule of GROUP BY

If you use GROUP BY, every column in your SELECT clause must either be included in the GROUP BY clause, or it must be wrapped in an aggregate function.

HAVING

What if you want to filter the results of an aggregation? You cannot use the WHERE clause for this, because WHERE filters rows before the aggregation happens.

To filter after aggregation, you use the HAVING clause.

SELECT 
    department, 
    COUNT(id) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(id) > 10;

(This returns only departments that have more than 10 employees).

Execution Order

Understanding the execution order of a SQL query is vital for debugging:

1. FROM and JOINs determine the base dataset.
2. WHERE filters the raw rows.
3. GROUP BY aggregates the filtered rows.
4. HAVING filters the aggregated results.
5. SELECT chooses which columns/calculations to return.
6. ORDER BY sorts the final result set.
7. LIMIT / OFFSET trims the final output.