SQL Aggregate Functions: Syntax, Usage, and Examples

SQL aggregate functions perform calculations on a set of values and return a single result. They are commonly used with the SELECT statement to summarize data, such as finding totals, averages, or counts.

How to Use SQL Aggregate Functions

SQL aggregate functions operate on multiple rows and return a single value. They work with numeric, string, or date values and are often used with GROUP BY and HAVING clauses.

Basic Syntax

Aggregate functions are typically used in SELECT queries:

SELECT AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE condition;

For example, to get the total salary from an employees table:

SELECT SUM(salary) FROM employees;

You can also use GROUP BY to aggregate data for each group:

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

When to Use SQL Aggregate Functions

Aggregate functions are useful when analyzing large datasets. Here are some common use cases.

Counting Records in a Table

To count the total number of users in a system, you can use COUNT():

SELECT COUNT(*) FROM users;

This function helps track user growth, sales transactions, or any other dataset where you need a total count.

Summarizing Financial Data

To calculate total sales revenue in a month, use SUM():

SELECT SUM(total_price) FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

Businesses use this function to track revenue, expenses, and financial reports.

Finding Maximum and Minimum Values

To find the highest salary in a company, use MAX():

SELECT MAX(salary) FROM employees;

To find the lowest price of a product:

SELECT MIN(price) FROM products;

This is useful for setting salary benchmarks or tracking pricing strategies.

Examples of SQL Aggregate Functions

Using COUNT() to Count Rows

To count how many employees are in each department:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

This returns the number of employees per department.

Using SUM() for Total Sales

To calculate total revenue from orders:

SELECT SUM(amount) AS total_revenue FROM orders;

This is useful for financial reporting and tracking sales performance.

Using AVG() for Performance Metrics

To find the average score of students in an exam:

SELECT AVG(score) AS average_score FROM exam_results;

This helps educators analyze student performance.

Using STRING_AGG() for Concatenating Values

To list all product names in a category as a single string (available in SQL Server and PostgreSQL):

SELECT category, STRING_AGG(product_name, ', ') AS product_list
FROM products
GROUP BY category;

This is useful for displaying grouped values in reports.

Learn More About SQL Aggregate Functions

Using WHERE and HAVING with Aggregate Functions

A WHERE clause filters rows before aggregation, while a HAVING clause filters aggregated results.

Filtering Before Aggregation (WHERE)

To calculate the total salary for employees in a specific department:

SELECT SUM(salary)
FROM employees
WHERE department = 'Sales';

Filtering After Aggregation (HAVING)

To show only departments where the average salary is above 50,000:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Combining Aggregate Functions in a Query

You can use multiple aggregate functions in the same query. To find total employees, the highest salary, and average salary:

SELECT COUNT(*) AS total_employees,
       MAX(salary) AS highest_salary,
       AVG(salary) AS average_salary
FROM employees;

Performance Considerations

• Indexing can speed up queries involving aggregate functions.
• Using aggregate functions on large datasets can slow performance; consider using partitions or indexing strategies.