SQL AVG Function: Syntax & Examples Explained
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.
Frequently Asked Questions (FAQs)
1. What does the AVG function do in SQL?
The SQL AVG() function calculates the average (mean) value of a numeric column or expression by summing all non-NULL values and dividing by the number of non-NULL rows. It is commonly used in analytics, reporting and performance measurement.
2. Which data types are supported by the AVG function?
AVG works only with numeric data types such as INT, BIGINT, DECIMAL, FLOAT and DOUBLE. It cannot be applied to text or date columns and non-numeric input will result in an error.
3. How does AVG handle NULL values?
AVG ignores NULL values by default. Rows with NULL values are excluded from both the sum and the count. If all values are NULL, AVG returns NULL. Use COALESCE(column,0) if NULLs must be treated as zero.
4. What is the difference between AVG(column) and AVG(expression)?
AVG(column) calculates the average of stored column values, while AVG(expression) computes the average of calculated values such as formulas or conditional logic, enabling advanced business calculations.
5. Can AVG be used with the WHERE clause?
Yes. WHERE filters rows before AVG is applied, allowing averages to be calculated for specific subsets such as departments, regions or order statuses.
6. How does AVG work with GROUP BY?
With GROUP BY, AVG calculates a separate average for each group. This is used for insights like department-wise average salary or category-wise average price.
7. Can AVG be combined with ORDER BY?
Yes. ORDER BY can sort AVG results after aggregation, making it useful for ranking groups by highest or lowest averages.
8. What is conditional averaging in SQL?
Conditional averaging uses CASE WHEN inside AVG to apply business rules during aggregation, such as weighted averages, conditional bonuses or filtered calculations.
9. Can AVG return zero?
AVG returns zero only if all evaluated values are zero. If all values are NULL, it returns NULL. Use COALESCE(AVG(column),0) to guarantee a numeric result.
10. Can AVG be used with JOIN queries?
Yes. AVG is often used with JOINs, but incorrect joins can multiply rows and distort results. Proper join conditions and GROUP BY usage are essential for accuracy.
11. Is AVG allowed in UPDATE or INSERT statements?
Yes. AVG can be used inside subqueries in UPDATE or INSERT statements, commonly for updating summary tables or analytics data.
12. Does AVG impact performance on large tables?
AVG is efficient, but performance depends on indexing, filtering and query design. Indexed numeric columns and selective WHERE clauses improve performance on large datasets.
13. What happens if AVG is used without GROUP BY?
Without GROUP BY, AVG returns a single row representing the average across the entire result set. Adding GROUP BY returns one row per group.
14. Is AVG commonly used in real-world applications?
Yes. AVG is widely used in payroll systems, analytics dashboards, financial reporting, e-commerce metrics and performance monitoring.
15. Is AVG important for SQL interviews?
Yes. AVG is a frequent interview topic, especially with GROUP BY, NULL handling, conditional aggregation and performance considerations.