AVG in SQL

DevSniper
Published : 16 July 2024

108 Views

AVG function is a aggregate function that is designed to compute the average of a specified column or expression. AVG operates on numeric data types (integer, decimal, floating-point) and it is particularly useful for performing aggregate calculations across datasets. This article provides a detailed overview of the AVG aggregate function with example. After reading this article you can use the AVG function easily and retrieve valuable data from a table.

Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

SELECT AVG(expression)
FROM table_name
WHERE condition;

Demo Database

CREATE TABLE `Employees` (
    `Id` INT PRIMARY KEY AUTO_INCREMENT,
    `Name` VARCHAR(50) NOT NULL,
    `Mobile` VARCHAR(20) NOT NULL,
    `JobTitle` VARCHAR(100) NOT NULL,
    `Department` VARCHAR(100) NOT NULL,
    `salary` DECIMAL(10, 2)
);

INSERT INTO Employees (Name, Mobile, JobTitle, Department, salary)
VALUES
    ('Ram', '9999999998', 'Software Engineer III', 'Engineering', 590000.00),
    ('Smith', '9999999958', 'Marketing Manager', 'Marketing', 10000.00),
    ('Deepak', '9999999238', 'Software Engineer II', 'Engineering', 130000.00),
    ('Jone', '9999999538', 'Software Engineer', 'Engineering', 30000.00),
    ('Gopal', '9999994238', 'Software Engineer II', 'Engineering', 120000.00),
    ('Jack', '9999999128', 'Sr. Marketing Manager', 'Marketing', 130000.00),
    ('Deepak Sharma', '9999945238', 'Software Engineer II', 'Engineering', 230000.00),
    ('Michael', '9999999698', 'Data Analyst', 'Analytics', 80000.00),
    ('Head', '9999999898', 'Data Analyst', 'Analytics', 90000.00),
    ('Jhon', '9999999978', 'HR Specialist', 'Human Resources', 75000.00);

AVG Example

AVG is a aggregate function, used to get the average of specified column or expression.

SQL query to get average salary of employees.

SELECT AVG(salary)
FROM Employees;

AVG(salary)
158500.000000

AVG with Expression

If you want to calculate the average of an expression in SQL, you can directly use the expression within the AVG function.

SQL query to get average salary after giving 20% hike without changing salary data in column. To get this adding 20% hike in salary and take average of that 20% hiked salary.

SELECT AVG(salary+salary*0.2)
FROM Employees;

AVG(salary+salary*0.2)
190200.0000000

Using an Alias with AVG

you can provide a column name of AVG column using AS keyword.

SELECT AVG(*) AS [average of records]
FROM table_name;

SQL query to get average salary of Employees table with average column alias `AvgSalary`

SELECT AVG(Salary) AS AvgSalary
FROM Employees;
    OR
SELECT AVG(Salary) AvgSalary
FROM Employees;

AvgSalary
158500.000000

AVG with WHERE clause

If you want to calculate average that satisfied certain condition( business logic). You can use AVG with WHERE clause.

SQL query to calculate Engineering department average salary.

SELECT AVG(Salary) AS AvgSalary
FROM Employees
WHERE Department ="Engineering";

AvgSalary
220000.000000

AVG with GROUP BY

It is useful, if you want to calculate the average of values within groups defined by one or more columns. This is useful for generating aggregated results for different categories or dimensions.

SQL query that returns department wise average salary.

SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY  Department;

Department      AvgSalary
Analytics	85000.000000
Engineering	220000.000000
Human Resources	75000.000000
Marketing	120000.000000

SQL query that returns department and job title wise average salary.

SELECT Department,JobTitle, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY  Department, JobTitle;

Department	JobTitle	           AvgSalary
Engineering	Software Engineer III	590000.000000
Marketing	Marketing Manager	10000.000000
Engineering	Software Engineer II	160000.000000
Engineering	Software Engineer	30000.000000
Marketing	Sr. Marketing Manager	130000.000000
Analytics	Data Analyst	        85000.000000
Human Resources	HR Specialist	        75000.000000

AVG with GROUP BY and ORDER BY

You can compute the average within groups and then order the results based on specific criteria. You can order result set as ascending or descending order based on your business requirement.

SQL query that compute the average salary, grouping the results by department and job title, and orders the result set by department

SELECT Department,JobTitle, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY  Department, JobTitle
ORDER BY Department;

Department	JobTitle	        AvgSalary
Analytics	Data Analyst	        85000.000000
Engineering	Software Engineer	30000.000000
Engineering	Software Engineer II	160000.000000
Engineering	Software Engineer III	590000.000000
Human Resources	HR Specialist	        75000.000000
Marketing	Marketing Manager	10000.000000
Marketing	Sr. Marketing Manager	130000.000000

Conditional Averaging

In SQL , Conditional Averaging involves calculating the average of values based on specified conditions. This can be achieved using the CASE statement within the AVG function or conditional aggregation using AVG along with CASE WHEN statements.

SQL query to compute average salary of Engineering department after hike where Software Engineer got 20% hike, Software Engineer II got 30% hike , Software Engineer III got 25% hike and other Job Title Employees did not got any hike.

SELECT SUM( 
CASE WHEN JobTitle = "Software Engineer" THEN Salary+Salary*0.2
   WHEN JobTitle = "Software Engineer II" THEN Salary+Salary*0.3
   WHEN JobTitle = "Software Engineer III" THEN Salary+Salary*0.25
ELSE Salary END
) as AvgSalary
FROM Employees
WHERE Department = "Engineering";

AvgSalary
1397500.0000

SQL query to compute average salary of Engineering department Job title wise after hike where Software Engineer got 20% hike, Software Engineer II got 30% hike , Software Engineer III got 25% hike and other Job Title Employees did not got any hike.

SELECT JobTitle, SUM( 
CASE WHEN JobTitle = "Software Engineer" THEN Salary+Salary*0.2
   WHEN JobTitle = "Software Engineer II" THEN Salary+Salary*0.3
   WHEN JobTitle = "Software Engineer III" THEN Salary+Salary*0.25
ELSE Salary END
) as AvgSalary
FROM Employees
WHERE Department = "Engineering"
GROUP BY JobTitle;

JobTitle	          AvgSalary
Software Engineer III	737500.0000
Software Engineer II	624000.0000
Software Engineer	36000.0000

Key Characteristics of AVG function

AVG function is used to compute the average of values within a specified column or expression.
AVG is commonly used with the SELECT.
Using AVG with GROUP BY allows us to compute average within groups defined by one or more columns. This is essential for generating aggregated statistics and summaries across various categories or dimensions in the dataset.
The AVG functions are crucial functions of SQL for robust data management and statistical analysis for generating reports, creating dashboards, and deriving insights from data stored in SQL databases.
NULL Handling : By default AVG ignores NULL values.
AVG function can be combined with other SQL functions such as GROUP BY, HAVING, and CASE expressions to perform complex calculations and derive meaningful insights from data.