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:
sql
SELECT AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE condition;
For example, to get the total salary from an employees table:
sql
SELECT SUM(salary) FROM employees;
You can also use GROUP BY to aggregate data for each group:
sql
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():
sql
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():
sql
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():
sql
SELECT MAX(salary) FROM employees;
To find the lowest price of a product:
sql
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:
sql
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:
sql
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:
sql
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):
sql
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:
sql
SELECT SUM(salary)
FROM employees
WHERE department = 'Sales';
Filtering After Aggregation (HAVING)
To show only departments where the average salary is above 50,000:
sql
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:
sql
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.