SQL Aggregate Functions

In this tutorial, we will learn and understand the SQL Aggregate Functions concept with the help of examples.

SQL Aggregate Function is a function used to perform calculation operations on one or more than one row of the single column of the table to return single data or multiple data in the output result sets.

The SQL Aggregate Function is used with the GROUP BY clause and the HAVING clause to group the SELECT query result.

The function which falls under the SQL aggregate functions is as follows:

1 Min() Function.

2 Max() Function.

3 Avg() Function.

4 Count() Function.

5 Sum() Function.

The NULL value is ignored by all the above functions like Min(), Max(), Sum(), and Avg() except Count() function.

The above Function ignored duplicate values if the distinct keyword is used in the Function. Else, duplicate records can't be ignored.

Now, let's learn about the SQL aggregate function concept with the help of syntax and examples.

Consider the already existing table name Diploma_Student:

Student_Id	Student_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id
202111	Vaishnavi Patil	94	91	88	85	95	92	91	1
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202113	Yash Dhull	90	88	94	87	85	90	89	3
202114	Sonali Patole	95	90	92	88	92	90	91	4
202115	Axar Patel	85	80	82	86	92	84	85	1
202116	Meena Mishra	78	75	80	74	85	77	78	3
202117	Mahesh Kumbhar	75	80	75	78	80	76	77	5
202118	Sakashi Patil	80	78	74	78	80	77	78	2
202119	Sopan Bhore	70	68	75	75	80	80	75	2
202220	Prajwal Lokhande	80	85	85	75	78	80	81	4
202221	Anuja Wanare	85	88	86	82	84	85	85	5
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202223	Anushka Sen	70	75	71	74	80	78	75	1
202224	Aakash Jain	80	75	72	74	85	80	78	4
202225	Akshay Agarwal	85	80	78	88	90	82	84	5

1 Count() Functions: The Count() Function displays the total number of data or rows from the table. The Count() function works with integer data and string data.

For example, Suppose I want to count how many students are present in the classroom whose first name start with the letter 'P'? I will search in the attendance sheet student's name starts with the letter 'P' and write it somewhere on the paper.

The syntax of the Count() Function is as follows:

SELECT Column_Name1, Column_Name2, Count(Column_Name) FROM Table_Name;

The above count function displays the total number of records of the specific columns, including null values.

Count(*) function returns all the table rows, including duplicates and NULL data.

Example 1: Execute a query to count the number of students who scored above 80 percent in the Sixth Sem.    

SELECT Count(Sixth_sem) AS 'Total Number of Students' FROM Diploma_Student WHERE Sixth_sem > 80;

In the above Count() query example, we return the total number of the students who scored above 80 percent in the sixth Sem.

The output of the above query is as follows:

Total Number of Students

8

Eight students scored greater than 80% in the sixth Sem.

Example 2: Execute a query to display the total number of the student whose names start with the letter 'A'

SELECT Count(Student_Name) AS 'Total Number' FROM Diploma_Student WHERE Student_Name LIKE 'A%';

In the above Count() query example, we return the total number of students whose names start with 'A'.

The output of the above query is as follows:

Total Number

5

There are five students whose name starts with the letter 'A'.

2 Sum() Functions: The Sum() Function is used to calculate the column's values.

The syntax of the Sum() Function is as follows:

SELECT Column_Name1, Column_Name2, Sum(Column_Name) FROM Table_Name;

Example 1: Execute a query to calculate the first Sem and the Sixth Sem sum.

SELECT SUM(First_Sem) AS 'Sum of First Sem ', SUM(Sixth_Sem) AS 'Sum of Sixth Sem ' FROM Diploma_Student;

In the Sum() function query example, we display the sum of the first sem column and the sum of the sixth sem column.

The output of the above query is as follows:

Sum of First Sem	Sum of Sixth Sem
1242	1244

Example 2: Execute a query to calculate the sum of all the columns from First Sem to Sixth Sem where student id is 202115.

SELECT SUM(First_Sem + Second_Sem + Third_Sem + Fourth_Sem + Fifth_Sem + Sixth_Sem)  AS 'Total Without Average' FROM Diploma_Student WHERE Student_Id = 202115;

In the above Sum() function query example, we display the sum of all the columns mentioned in the query where the student Id is 201115.

The output of the above query is as follows:

Total Without Average

509

3 Avg() Functions: The Avg() function is used to return the average value of the selected columns.

The syntax of the Avg() Function is as follows:

SELECT Column_Name1, Column_Name2, Avg(Column_Name) FROM Table_Name;

Example: Execute a query to return the average of the second Sem column.

SELECT AVG(Second_Sem)  AS ' Average of Second Sem' FROM Diploma_Student;

In the above Avg() function query example, we return the avg of the second Sem column.

The output of the above query is as follows:

Average of Second Sem

82.1333

4 Min() Function: The Min() Function is used to return the smallest value of the column.

The syntax of the Min() Function is as follows:

SELECT Column_Name1, Column_Name2, Min(Column_Name) FROM Table_Name;

Example: Execute a query to find the smallest value of the Third Sem.

SELECT MIN(Third_Sem)  AS 'Minimum Value' FROM Diploma_Student;

In the above Min() function query example, we return the smallest value of the third sem column.

The output of the above query is as follows:

Minimum Value

71

5 Max() Function: The Max() Function is used to return the largest value of the column.

The syntax of the Max() Function is as follows:

SELECT Column_Name1, Column_Name2, Max(Column_Name) FROM Table_Name;

Example: Execute a query to find the smallest value of the Third Sem.

SELECT MAX(Fourth_Sem)  AS 'Maximum Value' FROM Diploma_Student;

In the above max() function query example, we return the largest value of the fourth sem column.

The output of the above query is as follows:

Maximum Value

90