SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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_IdStudent_NameFirst_SemSecond_SemThird_SemFourth_SemFifth_SemSixth_SemTotalDepartment_Id
202111Vaishnavi Patil949188859592911
202112Vaibhav Lokhande859092808582862
202113Yash Dhull908894878590893
202114Sonali Patole959092889290914
202115Axar Patel858082869284851
202116Meena Mishra787580748577783
202117Mahesh Kumbhar758075788076775
202118Sakashi Patil807874788077782
202119Sopan Bhore706875758080752
202220Prajwal Lokhande808585757880814
202221Anuja Wanare858886828485855
202222Venkatesh Iyer908987909291903
202223Anushka Sen707571748078751
202224Aakash Jain807572748580784
202225Akshay Agarwal858078889082845

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
SQL Aggregate Functions

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
SQL Aggregate Functions

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 SemSum of Sixth Sem
12421244
SQL Aggregate Functions

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
SQL Aggregate Functions

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
SQL Aggregate Functions

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
SQL Aggregate Functions



ADVERTISEMENT
ADVERTISEMENT