SQL AVG() Function
The SQL AVG() function calculates the average value of a numeric column. It is used to find the mean value of data in a specific column.
Basic Usage
The AVG() function can be used to calculate the average of a column in a table.
SELECT AVG(salary) AS average_salary FROM employees;This query calculates the average salary of all employees in the employees table.
Using AVG() with WHERE Clause
The AVG() function can be used with a WHERE clause to calculate the average of a column for rows that match a specific condition.
SELECT AVG(salary) AS average_sales_salary
FROM employees
WHERE department = 'Sales';This query calculates the average salary of employees in the Sales department.
Using AVG() with GROUP BY
The AVG() function is often used with the GROUP BY clause to calculate the average of a column for each group.
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;This query calculates the average salary for each department.
Example 1: Calculating Average Sales
SELECT AVG(sale_amount) AS average_sales
FROM sales;This query calculates the average sale amount in the sales table.
Example 2: Calculating Average Sales by Product
SELECT product_id, AVG(sale_amount) AS average_sales
FROM sales
GROUP BY product_id;This query calculates the average sale amount for each product in the sales table.
Example 3: Calculating Average Sales by Region
SELECT region, AVG(sale_amount) AS average_sales
FROM sales
GROUP BY region;This query calculates the average sale amount for each region in the sales table.
Example 4: Using AVG() with HAVING Clause
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;This query calculates the average salary for each department and only returns departments where the average salary is greater than 50,000.
The AVG() function is a useful tool for calculating the mean value of numeric data in SQL. It can be used to find averages across entire tables or within specific groups, often in combination with WHERE, GROUP BY, and HAVING clauses.