SQL AVG

What is SQL AVG Function?

The AVG function in SQL calculates the average of numerical values within a specified column of a table. It helps in obtaining insights into the central tendencies of datasets and provides a quick and efficient way to analyze numeric data.

Syntax of SQL AVG Function

SELECT AVG(Numerical_Column_Name) FROM Table_Name;

Let's break down the components:

SELECT: Specifies the retrieval of data.

AVG: The aggregate function indicating we want to calculate the average.

Numerical_Column_Name: The specific column containing numeric values.

Table_Name: The name of the table from which data is extracted.

Example 1: Basic Usage

Let's consider a practical example with a table named Bikes_Details:

CREATE TABLE Car_Details  
(  
Car_Number INT PRIMARY KEY,  
Car_Model INT,  
Car_Name VARCHAR (50),  
Number_of_Car INT NOT NULL,  
Car_Price INT NOT NULL  
);

INSERT INTO Car_Details (Car_Number, Car_Model, Car_Name, Number_of_Car, Car_Price)   
VALUES
(2578, 2018, 'Aqua', 3, 1500000),
(9258, 2019, 'Axio', 2, 3000000),
;

-- Displaying the table
SELECT * FROM Car_Details;

Now, let's find the average price of cars:

SELECT AVG(Car_Price) AS "Average Car Price" FROM Car_Details;

This simple example illustrates the core functionality of the AVG function.

SQL AVG Function with WHERE Clause

The AVG function can be combined with the WHERE clause to calculate the average of filtered values. Consider the table Student_Details:

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

-- Inserting data into the table

INSERT INTO Student_Details VALUES
-- ... (student records)
;

-- Displaying the table --

SELECT * FROM College_Student_Details;

Now, let's find the average marks for students with scores above 90:

SELECT AVG(Student_Marks) AS "Average Marks Above 90" FROM Student_Details WHERE Student_Marks > 90;

SQL AVG Function with DISTINCT Clause

When dealing with distinct values, the AVG function can be enhanced using the DISTINCT clause. Using the same student table:

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

This refines the calculation to consider only unique values.

AVG Function with SQL GROUP BY Clause

In scenarios where data needs to be grouped, the GROUP BY clause comes into play. Using the Student_Course column:

SELECT Student_Course, AVG(Student_Marks) AS "Average Student Marks" FROM Student_Details GROUP BY Student_Course;

This groups the data by course, providing a detailed analysis.