# 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 |