SQL Aggregate functions
SQL Aggregate functions are functions where the values of multiple rows are grouped as input on certain criteria to form a single value result of more significant meaning.
It is used to summarize data, by combining multiple values to form a single result.
SQL Aggregate functions are mostly used with the GROUP BY clause of the SELECT statement.
Various Aggregate Functions
- Count()
- Sum()
- Avg()
- Min()
- Max()
Aggregate Functions in SQL
Below is the list of SQL aggregate functions, with examples
Count():
- Count(*): Returns the total number of records .i.e 6.
- Count(salary): Return the number of Non-Null values over the column salary. i.e 5.
- Count(Distinct Salary): Return the number of distinct Non-Null values over the column salary .i.e 5.
Sum():
- sum(salary): Sum all Non-Null values of Column salary i.e., 310
- sum(Distinct salary): Sum of all distinct Non-Null values i.e., 250.
Avg():
- Avg(salary) = Sum(salary) / count(salary) = 310/5
- Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 250/4
Min():
- Min(salary): Minimum value in the salary column except NULL i.e., 40.
Max():
- Max(salary): Maximum value in the salary i.e., 80.
Demo SQL Database
In this tutorial on aggregate functions, we will use the following table for examples:
Id | Name | Salary |
---|---|---|
1 | A | 802 |
2 | B | 403 |
3 | C | 604 |
4 | D | 705 |
5 | E | 606 |
6 | F | NULL |
You can also create this table on your system, by writing the following queries:
CREATE TABLE Employee (
Id INT PRIMARY KEY,
Name CHAR(1), -- Adjust data type and length if names can be longer than a single character
Salary DECIMAL(10,2) -- Adjust precision and scale if needed for salaries
);
INSERT INTO Employee (Id, Name, Salary)
VALUES (1, 'A', 802),
(2, 'B', 403),
(3, 'C', 604),
(4, 'D', 705),
(5, 'E', 606),
(6, 'F', NULL);
Aggregate Function Example
In this example, we will use multiple aggregate functions on the data.
Queries
--Count the number of employees
SELECT COUNT(*) AS TotalEmployees FROM Employee;
-- Calculate the total salary
SELECT SUM(Salary) AS TotalSalary FROM Employee;
-- Find the average salary
SELECT AVG(Salary) AS AverageSalary FROM Employee;
-- Get the highest salary
SELECT MAX(Salary) AS HighestSalary FROM Employee;
-- Determine the lowest salary
SELECT MIN(Salary) AS LowestSalary FROM Employee;
Output
TotalEmployees
6
TotalSalary
3120
AverageSalary
624
HighestSalary
802
LowestSalary
403
Key Takeaways about SQL Aggregate Functions
- Aggregate functions in SQL operate on a group of values and return a single result.
- They are often used with the GROUP BY clause to summarize the grouped data.
- Aggregate function operates on non-NULL values only (except COUNT).
- Commonly used aggregate functions are – MIN(), MAX(), COUNT(), AVG(), and SUM().