Aggregate Functions
Aggregate functions perform calculations on multiple rows and return a single summarized result.
What are Aggregate Functions?
Aggregate functions in SQL Server are used to perform calculations on a set of rows and return one summarized value.
They are commonly used with the GROUP BY clause when we need department-wise, category-wise, or other grouped results.
Aggregate functions take multiple records as input and produce a calculated result.
Common Aggregate Functions
| Function | Purpose | Example |
|---|---|---|
| COUNT() | Counts rows or values. | COUNT(*) |
| SUM() | Calculates the total. | SUM(Salary) |
| AVG() | Calculates the average. | AVG(Salary) |
| MIN() | Returns the smallest value. | MIN(Salary) |
| MAX() | Returns the largest value. | MAX(Salary) |
Example Table
Consider the following Employees table:
| EmployeeId | EmployeeName | Department | Salary |
|---|---|---|---|
| 1 | Rahul | IT | 50000 |
| 2 | Priya | HR | 40000 |
| 3 | Amit | IT | 60000 |
| 4 | Neha | HR | 45000 |
| 5 | Rohit | Sales | 35000 |
COUNT()
COUNT() is used to count rows or non-NULL values in a column.
SELECT COUNT(*) AS TotalEmployees
FROM Employees;
The above query returns the total number of employees.
TotalEmployees = 5
SUM()
SUM() calculates the total of numeric values.
SELECT SUM(Salary) AS TotalSalary
FROM Employees;
This query calculates the total salary of all employees.
TotalSalary = 230000
AVG()
AVG() calculates the average value of a numeric column.
SELECT AVG(Salary) AS AverageSalary
FROM Employees;
This query calculates the average salary of all employees.
AverageSalary = 46000
MIN()
MIN() returns the smallest value from a column.
SELECT MIN(Salary) AS MinimumSalary
FROM Employees;
MinimumSalary = 35000
MAX()
MAX() returns the largest value from a column.
SELECT MAX(Salary) AS MaximumSalary
FROM Employees;
MaximumSalary = 60000
Aggregate Functions with GROUP BY
Aggregate functions are frequently combined with GROUP BY to calculate results for each group.
SELECT
Department,
COUNT(*) AS EmployeeCount,
SUM(Salary) AS TotalSalary,
AVG(Salary) AS AverageSalary,
MIN(Salary) AS MinimumSalary,
MAX(Salary) AS MaximumSalary
FROM Employees
GROUP BY Department;
Important Points
- Aggregate functions work on multiple rows.
- They return a summarized result.
- COUNT() counts records or values.
- SUM() calculates totals.
- AVG() calculates averages.
- MIN() returns the smallest value.
- MAX() returns the largest value.
Summary
Aggregate functions are used to perform calculations on multiple rows and return summarized information. The most commonly used aggregate functions in SQL Server are COUNT(), SUM(), AVG(), MIN() and MAX().