Aggregate functions are used to perform calculations on multiple rows of a table and return a single summarized value. They are commonly used in reports, analytics, and group operations.
Common Aggregate Functions
|
Function
|
Description
|
|
COUNT()
|
Counts the number of rows
|
|
SUM()
|
Adds all numeric values
|
|
AVG()
|
Calculates the average value
|
|
MIN()
|
Finds the minimum value
|
|
MAX()
|
Finds the maximum value
|
Syntax Example
SELECT COUNT(*) AS total_employees,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary,
SUM(salary) AS total_salary
FROM employees;
Result:
total_employees | avg_salary | min_salary | max_salary | total_salary
----------------------------------------------------------------------
50 | 45000 | 25000 | 80000 | 2250000
Aggregate Functions with GROUP BY
To calculate aggregates for each group in a table:
SELECT department, COUNT(*) AS dept_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
Result:
Shows employee count and average salary for each department
Key Points
- Aggregate functions ignore NULL values (except COUNT(*))
- Can be combined with GROUP BY for grouped summaries
- Often used with HAVING to filter groups based on aggregate results
