Aggregate Functions in SQL

Aggregate functions in SQL are powerful tools that allow you to perform calculations on a set of values to return a single scalar value. They are commonly used with the GROUP BY clause to group rows that share a common attribute and then perform calculations on each group. Aggregate functions are essential for data analysis, reporting, and generating insights from your data.

After reading the material, you should be able to answer the following questions:

1. What are aggregate functions in SQL, and how are they typically used with the GROUP BY clause?
2. How does the COUNT function work, and what is the difference between COUNT(*) and COUNT(column)?
3. In what scenarios would you use the SUM, AVG, MIN, and MAX functions, and how do they operate on data?
4. What is the purpose of the HAVING clause, and how does it differ from the WHERE clause when filtering aggregated data?
5. How do window functions differ from aggregate functions, and what are some practical applications of window functions in SQL?

Common Aggregate Functions

Here are the most commonly used aggregate functions in SQL:

• The COUNT function calculates the number of rows in a dataset that meet a specified condition or the total number of rows when no condition is provided.
• The SUM function adds together all the numeric values in a specified column.
• The AVG function computes the average of all numeric values in a column by dividing the sum of the values by the number of non-NULL entries.
• The MIN function identifies the smallest value in a dataset, including numeric, string, and date types.
• The MAX function returns the largest value in a dataset, similar to MIN, and supports numeric, string, and date types.

Setting Up Example Tables

Suppose we have two tables: Employees and Departments.

Employees Table

Departments Table

COUNT Function

The COUNT function returns the number of rows that match a specified condition.

Example: Counting Total Employees

SELECT COUNT(*) AS TotalEmployees
FROM Employees;

Result

Example: Counting Employees per Department

SELECT DepartmentID, COUNT(*) AS NumberOfEmployees
FROM Employees
GROUP BY DepartmentID;

Result

• The GROUP BY clause groups the rows by DepartmentID.
• The COUNT(*) function counts the number of employees in each department.

SUM Function

The SUM function adds up all the values in a numeric column.

Example: Calculating Total Salary Expenditure

SELECT SUM(Salary) AS TotalSalary
FROM Employees;

Result

Example: Calculating Total Salary per Department

SELECT DepartmentID, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID;

Result

The SUM(Salary) function calculates the total salary for each department.

AVG Function

The AVG function calculates the average value of a numeric column.

Example: Calculating Average Salary

SELECT AVG(Salary) AS AverageSalary
FROM Employees;

Result

Example: Calculating Average Salary per Department

SELECT DepartmentID, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID;

Result

The AVG(Salary) function computes the average salary for each department.

MIN and MAX Functions

The MIN and MAX functions return the smallest and largest values in a set, respectively.

Example: Finding Minimum and Maximum Salaries

SELECT MIN(Salary) AS MinimumSalary, MAX(Salary) AS MaximumSalary
FROM Employees;

Result

Example: Finding Minimum and Maximum Salaries per Department

SELECT DepartmentID, MIN(Salary) AS MinimumSalary, MAX(Salary) AS MaximumSalary
FROM Employees
GROUP BY DepartmentID;

Result

The MIN(Salary) and MAX(Salary) functions find the lowest and highest salaries in each department.

GROUP BY Clause

The GROUP BY clause is used with aggregate functions to group the result set by one or more columns.

Example: Counting Employees by Department Name

To make the results more readable, let's join the Employees and Departments tables.

SELECT d.DepartmentName, COUNT(*) AS NumberOfEmployees
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName;

Result

• The JOIN clause combines the Employees and Departments tables.
• The GROUP BY clause groups the results by DepartmentName.

HAVING Clause

The HAVING clause is used to filter groups based on a condition, similar to how the WHERE clause filters rows.

Example: Departments with More Than One Employee

SELECT DepartmentID, COUNT(*) AS NumberOfEmployees
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 1;

Result

The HAVING clause filters groups where the count of employees is greater than one.

Combining Aggregate Functions

You can use multiple aggregate functions in a single query to get comprehensive insights.

Example: Employee Statistics per Department

SELECT
    d.DepartmentName,
    COUNT(*) AS NumberOfEmployees,
    MIN(e.Salary) AS MinimumSalary,
    MAX(e.Salary) AS MaximumSalary,
    AVG(e.Salary) AS AverageSalary,
    SUM(e.Salary) AS TotalSalary
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName;

Result

The query provides a comprehensive overview of salary statistics for each department.

Dealing with NULL Values

Aggregate functions generally ignore NULL values except for the COUNT(*) function.

Example: Impact of NULL on Aggregate Functions

Suppose we have an additional employee with a NULL salary.

Updated Employees Table

Query: Calculating Average Salary with NULL Values

SELECT DepartmentID, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID;

Result

• The AVG function ignores the NULL salary for Susan Miller.
• The average salary for department 1 remains the same.

Using DISTINCT with Aggregate Functions

The DISTINCT keyword can be used inside aggregate functions to consider only unique values.

Example: Counting Unique Salaries

SELECT COUNT(DISTINCT Salary) AS UniqueSalaries
FROM Employees;

Result

The COUNT(DISTINCT Salary) function counts the number of unique salary values, excluding NULL.

Aggregate Functions with Subqueries

Aggregate functions can be used in subqueries to compare individual rows to aggregate values.

Example: Employees Earning Above Average Salary

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
);

Result

• The subquery calculates the average salary.
• The outer query selects employees whose salary is greater than this average.

Window Functions (Analytic Functions)

In addition to aggregate functions, SQL supports window functions that perform calculations across a set of rows related to the current row.

Example: Calculating Running Total of Salaries

SELECT
    EmployeeID,
    FirstName,
    LastName,
    Salary,
    SUM(Salary) OVER (ORDER BY EmployeeID) AS RunningTotal
FROM Employees;

Result

• The SUM(Salary) OVER (ORDER BY EmployeeID) calculates a running total of salaries.
• NULL values are handled according to the window function's rules.

Practical Tips for Using Aggregate Functions

• Assign meaningful aliases to aggregate results to improve the readability of the output.
• Apply the WHERE clause to filter rows before performing aggregation, ensuring only relevant data is included in calculations.
• Be mindful that most aggregate functions (e.g., SUM, AVG) ignore NULL values, which might lead to unexpected results.
• Limit the number of columns in the GROUP BY clause to only those essential for your analysis, as excessive grouping can increase query complexity and runtime.
• Use the HAVING clause to filter groups after aggregation, allowing conditions based on aggregated results.