SQL Topics
Aggregate Functions
title: Aggregate Functions
In database systems, we often need summarized information rather than individual records. Businesses rarely want to view every transaction, employee record, or customer entry one by one. Instead, they typically need answers to questions such as:
- How many customers are there?
- What is the total revenue?
- What is the average salary?
- Which product has the highest price?
- Which student scored the lowest marks?
Calculating these values manually would be inefficient, especially when working with thousands or millions of records.
To solve this problem, SQL provides Aggregate Functions.
Aggregate Functions perform calculations on a group of rows and return a single summarized value. They are among the most important SQL features and are heavily used in reporting, analytics, dashboards, and business intelligence systems.
What are Aggregate Functions?
Aggregate Functions are built-in SQL functions that perform calculations on multiple rows and return a single result.
Example:
SELECT COUNT(*)
FROM Students;Result:
100Instead of displaying all student records, SQL returns the total number of students.
Why are Aggregate Functions Important?
Consider an Employees table:
| EmployeeID | Name | Salary |
|---|---|---|
| 1 | Rahul | 50000 |
| 2 | Priya | 60000 |
| 3 | Amit | 45000 |
Management may want:
Total Employees
Average Salary
Highest Salary
Lowest Salary
Total Salary ExpenseAggregate functions provide these answers instantly.
Common SQL Aggregate Functions
The most commonly used aggregate functions are:
| Function | Purpose |
|---|---|
| COUNT() | Counts records |
| SUM() | Calculates total value |
| AVG() | Calculates average value |
| MAX() | Finds highest value |
| MIN() | Finds lowest value |
These functions form the foundation of SQL analytics.
How Aggregate Functions Work
Consider:
| Salary |
|---|
| ---------- |
| 40000 |
| 50000 |
| 60000 |
Query:
SELECT AVG(Salary)
FROM Employees;Process:
40000
50000
60000
↓
Average Calculation
↓
50000SQL performs the calculation automatically.
COUNT() Function
The COUNT() function returns the number of rows.
Example:
SELECT COUNT(*)
FROM Employees;Result:
3This counts all records.
COUNT(ColumnName)
Example:
SELECT COUNT(Email)
FROM Customers;Only non-NULL email values are counted.
COUNT(DISTINCT)
Example:
SELECT COUNT(DISTINCT Department)
FROM Employees;Result:
5Only unique departments are counted.
SUM() Function
The SUM() function calculates the total of numeric values.
Example:
SELECT SUM(Salary)
FROM Employees;Result:
155000Useful for:
- Revenue calculations
- Salary expenses
- Sales analysis
AVG() Function
The AVG() function calculates the average value.
Example:
SELECT AVG(Salary)
FROM Employees;Result:
51666.67Useful for:
- Average salary
- Average marks
- Average sales
MAX() Function
The MAX() function returns the highest value.
Example:
SELECT MAX(Salary)
FROM Employees;Result:
60000Useful for identifying:
- Highest salary
- Highest score
- Highest revenue
MIN() Function
The MIN() function returns the smallest value.
Example:
SELECT MIN(Salary)
FROM Employees;Result:
40000Useful for finding:
- Lowest salary
- Lowest price
- Lowest score
Creating a Sample Table
Example:
CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100),
Department VARCHAR(50),
Salary DECIMAL(10,2)
);Insert data:
INSERT INTO Employees VALUES
(1, 'Rahul', 'IT', 50000),
(2, 'Priya', 'HR', 60000),
(3, 'Amit', 'IT', 45000),
(4, 'Neha', 'Finance', 70000);Using COUNT()
Query:
SELECT COUNT(*)
FROM Employees;Result:
4Four employees exist.
Using SUM()
Query:
SELECT SUM(Salary)
FROM Employees;Result:
225000Total salary expenditure.
Using AVG()
Query:
SELECT AVG(Salary)
FROM Employees;Result:
56250Average employee salary.
Using MAX()
Query:
SELECT MAX(Salary)
FROM Employees;Result:
70000Highest salary in the company.
Using MIN()
Query:
SELECT MIN(Salary)
FROM Employees;Result:
45000Lowest salary in the company.
Aggregate Functions with GROUP BY
Aggregate functions become even more powerful when combined with GROUP BY.
Example:
SELECT Department,
AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;Result:
| Department | AverageSalary |
|---|---|
| IT | 47500 |
| HR | 60000 |
| Finance | 70000 |
Each department receives its own calculation.
Aggregate Functions with WHERE
Example:
SELECT AVG(Salary)
FROM Employees
WHERE Department = 'IT';Result:
Only IT employees are included in the calculation.
Aggregate Functions with HAVING
Example:
SELECT Department,
COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;Result:
Only departments with more than five employees are displayed.
Aggregate Functions and NULL Values
Most aggregate functions ignore NULL values.
Example:
| Salary |
|---|
| ---------- |
| 50000 |
| NULL |
| 60000 |
Query:
SELECT AVG(Salary)
FROM Employees;Result:
55000The NULL value is ignored.
Real-World Example
Imagine an online store.
Orders table:
| OrderID | Amount |
|---|---|
| 1 | 500 |
| 2 | 1000 |
| 3 | 1500 |
Query:
SELECT
COUNT(*) AS TotalOrders,
SUM(Amount) AS TotalRevenue,
AVG(Amount) AS AverageOrderValue,
MAX(Amount) AS HighestOrder,
MIN(Amount) AS LowestOrder
FROM Orders;Result:
| Metric | Value |
|---|---|
| Total Orders | 3 |
| Total Revenue | 3000 |
| Average Order | 1000 |
| Highest Order | 1500 |
| Lowest Order | 500 |
This single query provides valuable business insights.
Common Errors
Using SUM() on Text Data
Wrong:
SELECT SUM(Name)
FROM Employees;SUM works only with numeric values.
Forgetting GROUP BY
Wrong:
SELECT Department,
AVG(Salary)
FROM Employees;Most databases require:
GROUP BY DepartmentMisunderstanding COUNT()
Example:
COUNT(ColumnName)does not count NULL values.
Best Practices
Use Aliases
Good:
SELECT AVG(Salary) AS AverageSalary
FROM Employees;Combine with GROUP BY
Creates meaningful reports.
Understand NULL Behavior
Most aggregate functions ignore NULL values.
Use Appropriate Data Types
Numeric functions should be used on numeric columns.
Optimize Large Queries
Indexes and proper filtering improve performance.
Common Interview Questions
What are Aggregate Functions?
Functions that perform calculations on multiple rows and return a single value.
Name five common Aggregate Functions.
COUNT()
SUM()
AVG()
MAX()
MIN()Does COUNT(*) count NULL values?
Yes.
It counts rows regardless of NULL values.
Does AVG() include NULL values?
No.
NULL values are ignored.
Summary
Aggregate Functions are among the most important SQL tools for summarizing and analyzing data. They allow developers, analysts, and businesses to calculate totals, averages, counts, and extreme values quickly and efficiently.
In this lesson, you learned:
- What Aggregate Functions are
- Why they are important
- COUNT()
- SUM()
- AVG()
- MAX()
- MIN()
- GROUP BY integration
- HAVING integration
- NULL handling
- Best practices
Mastering Aggregate Functions is essential because they form the foundation of SQL reporting, analytics, and business intelligence.
Next Step
Continue to the next lesson:
COUNT() Function →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Aggregate Functions.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, functions, aggregate
Related SQL Topics