SQL SELECT AVG

In this tutorial, we will learn about the aggregate function name avg() function concept in SQL with the help of examples.

The AVG() function is one of the aggregate functions in SQL. The AVG() function displays the average of the values mentioned in the expression. The AVG() function is a numeric function. The AVG() function allows only one parameter. The AVG() function ignore NULL values.

The syntax for the SELECT AVG() function is as follows:

SELECT Column_Name_1, Column_Name_2, Column_Name_3, AVG(Column_Name) FROM Table_Name WHERE Expression;

Let’s understand the AVG() function with the help of examples

Consider the already existing table, which has the following data:

Table Name:- D_Students

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	Sakshi 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
202226	Shwetali Bhagwat	90	80	85	88	90	80	86	1
202227	Mayuri Wagh	80	80	85	80	82	85	82	4
202228	Utkarsh Rokade	85	80	80	90	84	84	84	5

Example 1: Execute a query to find the average of the student’s first-semester column from the D_Stundets table.

SELECT AVG(First_Sem) AS 'First Semester Average' FROM D_Students;

We displayed the student's first-semester column average in the above SELECT AVG() function query example

The output of the above query is as follows:

First Semester Average

83.1667

Example 2: Execute a query to find the average of the student’s total column group by the department id.

SELECT Department_Id, AVG(Total) AS 'Total Average' FROM D_Students GROUP BY Department_Id;

In the above SELECT AVG() function query example, we displayed the student’s total column average group by department id.

The output of the above query is:

Department_Id	Total Average
1	84.2500
2	79.6667
3	85.6667
4	83.0000
5	82.5000

Example 3: Execute a query to find the average student's first-semester to sixth-semester column group by the student name.

SELECT Student_Id, Student_Name, AVG(First_Sem + Second_Sem +Third_Sem + Fourth_Sem + Fifth_Sem + Sixth_Sem)/6 AS 'OverAll Average' FROM D_Students GROUP BY Student_Name;

In the above SELECT AVG() function query example, we display the student's first-semester to sixth-semester average group by the student name. We have used six columns as one parameter in the average function.

The output of the above query is as follows:

Student_Id	Student_Name	OverAll Average
202224	Aakash Jain	77.66666667
202225	Akshay Agarwal	83.83333333
202221	Anuja Wanare	85.00000000
202223	Anushka Sen	74.66666667
202115	Axar Patel	84.83333333
202117	Mahesh Kumbhar	77.33333333
202227	Mayuri Wagh	82.00000000
202116	Meena Mishra	78.16666667
202220	Prajwal Lokhande	80.50000000
202118	Sakshi Patil	77.83333333
202226	Shwetali Bhagwat	85.50000000
202114	Sonali Patole	91.16666667
202119	Sopan Bhore	74.66666667
202228	Utkarsh Rokade	83.83333333
202112	Vaibhav Lokhande	85.66666667
202111	Vaishnavi Patil	90.83333333
202222	Venkatesh Iyer	89.83333333
202113	Yash Dhull	89.00000000

Example 4: Execute a query to find the average of the student’s first-semester to sixth-semester column group by the student’s name and average greater than75.

SELECT Student_Id, Student_Name, AVG(First_Sem + Second_Sem +Third_Sem + Fourth_Sem + Fifth_Sem + Sixth_Sem)/6 AS 'OverAll Average' FROM D_Students GROUP BY Student_Name HAVING AVG(First_Sem + Second_Sem +Third_Sem + Fourth_Sem + Fifth_Sem + Sixth_Sem)/6 > 75;

In the above SELECT AVG() function query example, we display the student's first-semester to sixth-semester average group by the student name, and the average is greater than 75. The HAVING clause is used in the query.

The output of the above query is as follows:

Student_Id	Student_Name	OverAll Average
202224	Aakash Jain	77.66666667
202225	Akshay Agarwal	83.83333333
202221	Anuja Wanare	85.00000000
202115	Axar Patel	84.83333333
202117	Mahesh Kumbhar	77.33333333
202227	Mayuri Wagh	82.00000000
202116	Meena Mishra	78.16666667
202220	Prajwal Lokhande	80.50000000
202118	Sakshi Patil	77.83333333
202226	Shwetali Bhagwat	85.50000000
202114	Sonali Patole	91.16666667
202228	Utkarsh Rokade	83.83333333
202112	Vaibhav Lokhande	85.66666667
202111	Vaishnavi Patil	90.83333333
202222	Venkatesh Iyer	89.83333333
202113	Yash Dhull	89.00000000