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

SELECT AVG(Numerical_Column_Name) FROM Table_Name ;

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:

CREATE TABLE Bikes_Details
(
Bike_Number INT PRIMARY KEY,
Bike_Model INT,
Bike_Name VARCHAR (50),
Number_of_Bikes INT NOT NULL
Bike_Price INT NOT NULL
) ;

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

INSERT INTO Bikes_Details (Bike_Number, Bike_Model, Bike_Name, Number_of_Bikes, Bike_Price)
VALUES (2578, 2018, KTM, 3, 1500000),
(9258, 2019, Apache, 2, 3000000),
(8233, 2018, Pulsar, 6, 900000),
(8990, 2018, RX100, 7, 700000),
(9578, 2020, Splender+, 6, 8000000),
(1258, 2021, Bullet, 2, 1500000),
(2564, 2019, Passion, 4, 6000000),
(2155, 2020, Livo, 8, 1800000);

The following SELECT query shows the data ofthe Bikes_Details table:

SELECT * FROM Bikes_Details;

Bike_Number	Bike_Model	Bike_Name	Number_of_Bikes	Bike_Price
2578	2018	KTM	3	900000
9258	2019	Apache	2	1100000
8233	2018	Pulsar	6	900000
8990	2018	RX100	7	700000
9578	2020	Splender+	6	8000000
1258	2021	Buller	2	1500000
2564	2019	Passion	4	6000000
2155	2020	Livo	8	1800000

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

SELECT AVG(Bike_Price) As "Average of Bike's Price " FROM Bikes_Details;

Output:

SQL SELECT AVG

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

SELECT AVG(Numerical_Column_Name) FROM Table_Name WHERE [ Condition ];

Example of SQL Avg Function with WHERE clause

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

CREATE TABLE College_Student_Details
(
Student_ID INT NOT NULL,
Student_Name varchar(100),
Student_Course varchar(50),
Student_Age INT,
Student_Marks INT
);

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

INSERT INTO College_Student_Details VALUES (101, Anuj, B.tech, 20, 88);
INSERT INTO College_Student_Details VALUES (102, Raman, MCA, 24, 98);
INSERT INTO College_Student_Details VALUES (104, Shyam, BBA, 19, 92);
INSERT INTO College_Student_Details VALUES (107, Vikash, B.tech, 20, 88);
INSERT INTO College_Student_Details VALUES (111, Monu, MBA, 21, 65);
INSERT INTO College_Student_Details VALUES (114, Jones, B.tech, 18, 93);
INSERT INTO College_Student_Details VALUES (121, Parul, BCA, 20, 97);
INSERT INTO College_Student_Details VALUES (123, Divya, B.tech, 21, 98);
INSERT INTO College_Student_Details VALUES (128, Hemant, MBA, 23, 90);
INSERT INTO College_Student_Details VALUES (130, Nidhi, BBA, 20, 65);
INSERT INTO College_Student_Details VALUES (132, Priya, MBA, 22, 99);
INSERT INTO College_Student_Details VALUES (138, Mohit, MCA, 21, 92);

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

SELECT * FROM College_Student_Details;

Student_ID	Student_Name	Student_Course	Student_Age	Student_Marks
101	Anuj	B.tech	20	88
102	Raman	MCA	24	98
104	Shyam	BBA	19	92
107	Vikash	B.tech	20	88
111	Monu	MBA	21	65
114	Jones	B.tech	18	93
121	Parul	BCA	20	97
123	Divya	B.tech	21	98
128	Hemant	MBA	23	90
130	Nidhi	BBA	20	65
132	Priya	MBA	22	99
138	Mohit	MCA	21	88

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

SELECT AVG(Student_Marks) As "Average Student_Marks Greater 90" FROM College_Student_Details WHERE Student_Marks > 90;

Output:

SQL SELECT AVG

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:

SELECT AVG (DISTINCT (Column_Name)) FROM Table_Name;

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:

SELECT AVG (DISTINCT (Student_Marks)) AS "Average Distinct Student Marks" FROM College_Student_Details;

Output:

SQL SELECT AVG

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:

SELECT Column_Name_1, AVG(Column_Name) FROM Table_Name GROUP BY Column_Name_1;

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:

SELECT Student_Course, AVG(Student_Marks) FROM Table_Name GROUP BY Student_Course;

Output:

Student_Course	AVG (Student_Marks)
B.tech	91
MCA	93
BBA	78
MBA	84
BCA	97

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz

SQL SELECT AVG

Syntax of SQL Avg() Function

Two Examples of SQL Avg Function

SQL AVG Function With WHERE clause

Example of SQL Avg Function with WHERE clause

SQL AVG Function with DISTINCT clause

AVG Function with SQL GROUP BY clause

Comment / Reply From

You May Also Like

Popular Post

Language

Newsletter