Overview of AVG() with SQL

Overview of AVG()

The AVG() function in SQL is used to calculate the average of the values in a specified column. It is an aggregation function that helps you find the mean value of a dataset, such as average sales, average salaries, average scores, and so on.

Syntax

Calculate the Average of All Values in a Column 

SELECT AVG(column_name)
FROM table;

AVG(column_name) calculates the average of all values in the specified column.

Calculate the Average with Conditions 

SELECT AVG(column_name)
FROM table
WHERE condition;

AVG(column_name) calculates the average of values in the specified column that meet the given condition.

Calculate the Average within Groups of Data 

SELECT grouping_column, AVG(column_name)
FROM table
GROUP BY grouping_column;

AVG(column_name) calculates the average of values in the specified column for each group defined by the grouping column.

Examples of Using AVG()

Example 1: Calculating the Average of a Column

Assume you have a table salaries with columns id, name, and salary: 

CREATE TABLE salaries (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

To calculate the average salary: 

SELECT AVG(salary) AS average_salary
FROM salaries;

Explanation:

AVG(salary) calculates the average value in the salary column.

Example 2: Calculating the Average with Conditions

To calculate the average salary for employees with more than 5 years of service (assuming there is a years_of_service column): 

SELECT AVG(salary) AS average_salary_long_term
FROM salaries
WHERE years_of_service > 5;

Explanation:

• AVG(salary) calculates the average salary for employees whose years of service is greater than 5.
• The WHERE clause filters the rows to include only those with more than 5 years of service.

Example 3: Calculating the Average within Groups of Data

Assume you have a table sales with columns region, sale_amount, and sale_date. To calculate the average sale amount for each region: 

SELECT region, AVG(sale_amount) AS average_sales
FROM sales
GROUP BY region;

Explanation:

• AVG(sale_amount) calculates the average sale amount for each region.
• GROUP BY region groups the results by region, allowing AVG() to compute the average for each group.

Example 4: Calculating the Average Scores of Students

Suppose you have a table grades with columns student_id, subject, and score. To calculate the average score for each subject: 

SELECT subject, AVG(score) AS average_score
FROM grades
GROUP BY subject;

Explanation:

• AVG(score) calculates the average score for each subject.
• GROUP BY subject groups the results by subject, allowing AVG() to calculate the average for each subject.

Key Points

• Numeric Columns: The AVG() function can only be used on numeric columns. Non-numeric or NULL values in the column are not included in the average calculation.
• NULL Values: NULL values in the column are ignored by the AVG() function. Only non-NULL values are considered in the average calculation.
• Performance: Calculating the average on large tables, especially with complex queries or joins, can be resource-intensive. Ensure your queries are optimized for better performance when necessary.

Conclusion

The AVG() function is a powerful tool for calculating average values in your SQL data. Whether you need a global average, a conditional average, or averages within groups, AVG() provides a straightforward way to compute these statistical values.