SQL SELECT AVG

The AVG is an aggregate function in SQL which calculates the average of values of the numerical column from the table.

Syntax of SQL Avg() Function

Two Examples of SQL Avg Function

Example 1: This example describes how to use the AVG function in the SQL table.

First, we have to create a new table in the database on which MIN function is to be run.

The following CREATE TABLE statement creates the Bikes_Details table with five fields:

The following INSERT query inserts the record of cars into the Bikes_Details table:

The following SELECT query shows the data ofthe Bikes_Details table:

The following query finds the average of values of the Bike_Price column from the above Bikes_Details table:

Output:

SQL AVG Function With WHERE clause

The SQL Avg() function can also be used with the WHERE clause in the SELECT query for calculating the average of filtered values.

Syntax of AVG Function With WHERE clause

Example of SQL Avg Function with WHERE clause

The following query creates the College_Student_Details table using the CREATE TABLE statement:

The following SQL queries insert the record of students into the above table using INSERT INTO statement:

Let's see the record of the above table using the following SELECT statement:

The following AVG query calculates the average of those Student_Marks which are greater than 90 in the above table:

Output:

SQL AVG Function with DISTINCT clause

The AVG Function with Distinct clause in the SELECT statement calculates the average of distinct values of the column.

The syntax to use Distinct Clause with AVG Function is given below:

Example of AVG Function with DISTINCT Clause

We take the above College_Student_Details table to understand the concept of AVG with the DISTINCT Clause.

The following query calculates the average of distinct values of the Student_Marks column from the above College_Student_Details table:

Output:

AVG Function with SQL GROUP BY clause

In some situations, we have to use the GROUP BY clause with the AVG function in the SELECT statement. The AVG Function with GROUP BY clause calculates the average of distinct values of the same group.

The syntax to use Distinct Clause with AVG Function is given below:

Example of AVG Function with GROUP BY Clause

Let's take the above College_Student_Details table to understand the concept of AVG with GROUP BY Clause.

The following query calculates the average student marks by course from the above College_Student_Details table:

Output: