SQL Aggregate Functions

Last Updated : 10 Feb 2026

SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. It returns a single value.
It is also used to summarize the data.

Why do we use Aggregate Functions?

A crucial part of database management systems are aggregate functions. They enable us to swiftly and effectively do computations on big data sets. These tasks include, for instance, managing inventory levels, generating statistical reports, and conducting financial analysis. Using aggregate functions also helps us comprehend the data we are dealing with better.

For instance, finding the total sales for a specific period of time or figuring out the average price of every product in our inventory is simple. We would have to manually filter through each data point if there were no aggregate functions, which would be laborious and prone to mistake.

All things considered, aggregate functions are necessary for anyone dealing with big data and trying to extract useful data from it.

Types of SQL Aggregation Function

1. COUNT FUNCTION

COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.
COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null.

Syntax

COUNT(*)
or
COUNT( [ALL|DISTINCT] expression )

Sample table:

PRODUCT_MAST

PRODUCT	COMPANY	QTY	RATE	COST
Item1	Com1	2	10	20
Item2	Com2	3	25	75
Item3	Com1	2	30	60
Item4	Com3	5	10	50
Item5	Com2	2	20	40
Item6	Cpm1	3	25	75
Item7	Com1	5	30	150
Item8	Com1	3	10	30
Item9	Com2	2	25	50
Item10	Com3	4	30	120

Example: COUNT()

SELECT COUNT(*)
FROM PRODUCT_MAST;

Output:

Example: COUNT with WHERE

SELECT COUNT(*)
FROM PRODUCT_MAST;
WHERE RATE>=20;

Output:

Example: COUNT() with DISTINCT

SELECT COUNT(DISTINCT COMPANY)
FROM PRODUCT_MAST;  

Output:

Example: COUNT() with GROUP BY

SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY;

Output:

Com1    5
Com2    3
Com3    2

Example: COUNT() with HAVING

SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY
HAVING COUNT(*)>2;

Output:

Com1    5
Com2    3

2. SUM Function

Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

Syntax

SUM()
or
SUM( [ALL|DISTINCT] expression )

Example: SUM()

SELECT SUM(COST)
FROM PRODUCT_MAST;

Output:

Example: SUM() with WHERE

SELECT SUM(COST)
FROM PRODUCT_MAST
WHERE QTY>3;

Output:

Example: SUM() with GROUP BY

SELECT SUM(COST)
FROM PRODUCT_MAST
WHERE QTY>3
GROUP BY COMPANY;

Output:

Com1    150
Com2    170

Example: SUM() with HAVING

SELECT COMPANY, SUM(COST)
FROM PRODUCT_MAST
GROUP BY COMPANY
HAVING SUM(COST)>=170;

Output:

Com1    335
Com3    170

3. AVG function

The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.

Syntax

AVG()
or
AVG( [ALL|DISTINCT] expression )

Example:

SELECT AVG(COST)
FROM PRODUCT_MAST;

Output:

67.00

4. MAX Function

MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.

Syntax

MAX()
or
MAX( [ALL|DISTINCT] expression )

Example:

SELECT MAX(RATE)
FROM PRODUCT_MAST;

5. MIN Function

MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.

Syntax

MIN()
or
MIN( [ALL|DISTINCT] expression )

Example:

SELECT MIN(RATE)
FROM PRODUCT_MAST;

Output:

Example of Aggregate Functions

SQL Code:

 --Count the number of students
SELECT COUNT(*) AS TotalStudents FROM Students;

-- Calculate the total marks
SELECT SUM(Marks) AS TotalMarks FROM Student;

-- Find the average salary
SELECT AVG(Marks) AS AverageMarks FROM Student;

-- Get the highest salary
SELECT MAX(Marks) AS HighestMarks FROM Student;

-- Determine the lowest marks
SELECT MIN(Marks) AS LowestMarks FROM Student;

Output:

TotalStudents
9
TotalMarks
900
AverageMarks
400
HighestMarks
800
LowestMarks
300

Conclusion:

In SQL, an aggregate function calculates several values and returns a single result. Numerous aggregate functions, such as avg, count, sum, min, max, and others, are available in SQL. With the exception of the count function, an aggregate function conducts the computation ignoring NULL values.

Next TopicDBMS SQL Join

← prev next →

We deliver comprehensive tutorials, interview question-answers, MCQs, study materials on leading programming languages and web technologies like Data Science, MEAN/MERN full stack development, Python, Java, C++, C, HTML, React, Angular, PHP and much more to support your learning and career growth.