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

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:

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:

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:

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: