Aggregate Functions in SQL (HL)

What Are SQL Aggregate Functions?

Aggregate functions perform calculations on a set of values and return a single result.

They are commonly used in reporting and decision-making by summarising large amounts of data.

Common SQL Aggregate Functions

Function	Purpose	Example Usage
`AVG()`	Returns the average of a numeric column	`SELECT` `AVG`(Salary) `FROM` Employees;
`COUNT()`	Counts the number of rows	`SELECT` `COUNT`(*) `FROM` Orders;
`MAX()`	Returns the highest value in a column	`SELECT` `MAX`(Price) `FROM` Products;
`MIN()`	Returns the lowest value in a column	`SELECT` `MIN`(Price) `FROM` Products;
`SUM()`	Returns the total sum of a numeric column	`SELECT` `SUM`(Sales) `FROM` Revenue;

Constructing SQL Queries with Aggregate Functions

Example: Employee Salary Database

        Employees(EmployeeID, Name, Department, Salary)
    

Sample Data: Employees

EmployeeID	Name	Department	Salary
1	Alice	HR	52000
2	Bob	IT	63000
3	Charlie	Sales	47000
4	Diana	IT	59000
5	Eli	Sales	53000
6	Fiona	HR	51000

Aggregate Functions

Switch tabs to compare common aggregate queries and their outputs.

Calculating the Average Salary

                        SELECT AVG(Salary) AS AverageSalary

                        FROM Employees;

Query Result: Average Salary

Why these rows?
Aggregates across all rows in Employees.Salary. NULL salaries are ignored by AVG, producing the single overall mean.

AverageSalary
54166.67

Average Salary by Department

                        SELECT Department, AVG(Salary) AS AverageSalary

                        FROM Employees

                        GROUP BY Department;

Query Result: Average Salary by Department

Why these rows?
Groups rows by Department and returns one result per group. Within each group, AVG ignores NULLs and computes that department’s mean salary.

Department	AverageSalary
HR	51500.00
IT	61000.00
Sales	50000.00

Counting the Number of Employees

                        SELECT COUNT(*) AS TotalEmployees

                        FROM Employees;

Query Result: Total Employees

Why these rows?
COUNT(*) returns one row with the total number of rows in Employees (6). Use COUNT(Salary) to count only rows where Salary is not NULL.

TotalEmployees
6

Finding the Highest Salary

                        SELECT MAX(Salary) AS HighestSalary

                        FROM Employees;

Query Result: Highest Salary

Why these rows?
Returns a single row with the maximum non-NULL value in Employees.Salary.

HighestSalary
63000

Finding the Lowest Salary

                        SELECT MIN(Salary) AS LowestSalary

                        FROM Employees;

Query Result: Lowest Salary

Why these rows?
Returns a single row with the minimum non-NULL value in Employees.Salary.

LowestSalary
47000

Summing the Total Salary Expense

                        SELECT SUM(Salary) AS TotalSalaryExpense

                        FROM Employees;

Query Result: Total Salary Expense

Why these rows?
Adds up all non-NULL values in Employees.Salary to produce a single total.

TotalSalaryExpense
325000

Filtering Groups Using HAVING (Departments with Avg Salary > 50000)

        SELECT Department, AVG(Salary) AS AverageSalary

        FROM Employees

        GROUP BY Department

        HAVING AVG(Salary) > 50000;

Query Result: Departments with Avg Salary > 50000

Department	AverageSalary
HR	51500.00
IT	61000.00

Why Are Aggregate Functions Important?

Summarising Large Data Sets: Helps in analysing trends and patterns.
Efficient Reporting: Used in dashboards and business intelligence tools.
Decision Making: Assists businesses in data-driven decisions.
Optimised Queries: Reduces the need for multiple queries by aggregating results.

Key Takeaways

SQL aggregate functions like AVG, COUNT, MAX, MIN, SUM perform calculations on grouped data.
Used in conjunction with GROUP BY and HAVING for data analysis.
Aggregate functions help summarise large datasets efficiently for reporting and decision-making.
Using aggregate functions optimises SQL queries for performance and scalability.