SQL - Aggregate Functions

SQL - Summarizing Data with Aggregate Functions

Aggregation is the process of taking many values and summarizing them into a single value. Think of calculating a class average from individual test scores—that’s aggregation. SQL provides powerful aggregate functions to perform calculations on sets of rows, which is fundamental for analytics and reporting.

The Five Core Aggregate Functions

While SQL offers many aggregate functions, these five are the most common and form the foundation of data summarization. For our examples, we’ll use an Employees table:

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2),
    Bonus DECIMAL(10,2)
);

INSERT INTO Employees VALUES
(1, 'Alice', 'Engineering', 90000, 5000),
(2, 'Bob', 'Engineering', 85000, 4000),
(3, 'Charlie', 'Sales', 75000, 10000),
(4, 'David', 'Sales', 78000, 12000),
(5, 'Eve', 'HR', 60000, NULL); -- Note the NULL bonus

• COUNT(): Counts the number of rows. COUNT(*) counts all rows. COUNT(column) counts non-NULL values in that column. SELECT COUNT(*) FROM Employees; → 5. SELECT COUNT(Bonus) FROM Employees; → 4.
• SUM(): Calculates the sum of a numeric column, ignoring NULL values. SELECT SUM(Salary) FROM Employees; → 388000.
• AVG(): Calculates the average of a numeric column, ignoring NULL values. SELECT AVG(Salary) FROM Employees; → 77600.
• MAX(): Returns the maximum value in a column. SELECT MAX(Salary) FROM Employees; → 90000.
• MIN(): Returns the minimum value in a column. SELECT MIN(Salary) FROM Employees; → 60000.

Aggregating in Groups: The GROUP BY Clause

The true power of aggregation is unlocked when combined with the GROUP BY clause. GROUP BY is used to arrange identical data into groups, allowing you to apply aggregate functions to each group independently.

Example: Average Salary Per Department

SELECT
    Department,
    AVG(Salary) AS AverageSalary,
    COUNT(*) AS NumberOfEmployees
FROM
    Employees
GROUP BY
    Department;

This query first groups the employees by their department and then calculates the average salary and employee count for each of those groups.

Filtering Groups: The HAVING Clause

How do you filter results based on an aggregate value? You can’t use WHERE, because WHERE filters rows before aggregation happens. For this, you need the HAVING clause, which filters groups after aggregation is complete.

Example: Departments with High Average Salaries

Let’s find only those departments where the average salary is greater than $80,000.

SELECT
    Department,
    AVG(Salary) AS AverageSalary
FROM
    Employees
GROUP BY
    Department
HAVING
    AVG(Salary) > 80000;

This would return only the ‘Engineering’ department.

Counting Unique Values: COUNT(DISTINCT)

A very common requirement is to count the number of unique values in a column. This is done by adding the DISTINCT keyword inside the COUNT function.

-- How many unique departments are there?
SELECT COUNT(DISTINCT Department) FROM Employees; -- Result: 3

Putting It All Together: The Logical Order of SQL Operations

Understanding the logical order in which a database processes a query is key to avoiding errors. Even though you write SELECT first, the database executes clauses in a different sequence:

1. FROM / JOIN: Gathers the source data.
2. WHERE: Filters individual rows.
3. GROUP BY: Groups the filtered rows.
4. HAVING: Filters the resulting groups.
5. SELECT: Selects the final columns and performs expressions.
6. DISTINCT: Removes duplicate rows from the result.
7. ORDER BY: Sorts the final result set.
8. LIMIT / OFFSET: Restricts the number of rows returned.

A Look Ahead: Introduction to Window Functions

What if you want to perform a calculation across a set of rows but still return all the original rows? For example, showing each employee’s salary alongside the average salary of their department. This is where window functions come in. They are a modern, powerful feature that performs aggregate-like calculations over a ‘window’ of rows related to the current row, without collapsing them. An example is AVG(Salary) OVER (PARTITION BY Department).