SQL AVG() Function

The AVG() function in SQL is an aggregate function used to calculate the average (mean) value of a numeric column in a table. It provides insights into the central tendency of numerical data, making it an essential tool for statistical analysis and reporting. The function automatically excludes NULL values during its computation, ensuring accurate results.

In this article, we will explain the syntax, use cases, sample tables, practical examples, and advanced techniques to use the AVG() function effectively. This article will help both beginners and experienced SQL users understand and implement the function in various scenarios.

Syntax:

SELECT AVG(column_name)
FROM table_name
[WHERE condition];

Key Terms:

• column_name: The numeric column for which you want to calculate the average.
• table_name: The name of the table containing the data.
• condition (optional): A condition to filter rows before calculating the average.

Examples of SQL AVG() Function

We will use the following two sample tables for demonstration. The products table contains product IDs, names, categories, and prices. The College_Student_Details table contains student details, including their marks and courses.

Products Table:

College_Student_Details Table:

Example 1: Calculate the Average Price of All Products

This example demonstrates how to calculate the average price of all products in the Products table. This calculation excludes any NULL values, ensuring accurate results.

Query:

SELECT AVG(Price) AS [Average Price]
FROM Products;

Output:

Explanation: The AVG() function calculates the mean of all values in the Price column, ignoring NULL values. The result provides the overall average price of the products.

Example 2: Calculate the Average Price with a Condition

This example demonstrates calculating the average price of products within a specific category.

Query:

SELECT AVG(Price) AS [Average Price for Category 2]
FROM Products
WHERE CategoryID = 2;

Output:

Explanation: The WHERE clause filters rows to include only products in CategoryID = 2. function then calculates the average price for the filtered data. This result helps identify pricing trends within specific product categories.

Example 3: Use AVG() with an Alias

This example shows how to use the AS keyword to assign a custom name to the result column.

Query:

SELECT AVG(Price) AS [Average Product Price]
FROM Products;

Output:

Explanation: The AS keyword is used to assign a descriptive name, making the result easier to interpret. This improves the readability of the query output, especially in reports or dashboards.

Example 4: Products with Higher Than Average Price

This example demonstrates how to find products priced higher than the average. This type of analysis is useful for identifying premium or higher-priced items.

Query:

SELECT *
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

Output:

Explanation: The subquery (SELECT AVG(Price) FROM Products) calculates the average price. The main query retrieves all products priced above this averagege.

Example 5: Using AVG() with GROUP BY

To calculate the average marks for students in each course:

Query:

SELECT Student_Course, AVG(Student_Marks) AS [Average Marks]
FROM College_Student_Details
GROUP BY Student_Course;

Output:

Explanation: The GROUP BY clause groups the rows by Student_Course, and the AVG(Student_Marks) function calculates the average marks for each group,, providing insights into academic performance by course.

Example 6: Using AVG() with DISTINCT

This example calculates the average of distinct marks in the Student_Marks column. This is helpful in scenarios where duplicate data might skew results.

Query:

SELECT AVG(DISTINCT Student_Marks) AS [Average Distinct Marks]
FROM College_Student_Details;

Output:

Explanation: The DISTINCT keyword ensures that only unique values in the Student_Marks column are considered when calculating the average.

Conclusion

The AVG() function is an essential SQL aggregate function that helps calculate the mean value of numeric data in a table. Whether analyzing product prices, student marks, or other numerical datasets, the AVG() function provides valuable insights into the central tendency. By combining it with clauses like WHERE, GROUP BY, and DISTINCT, we can perform advanced data analysis and generate meaningful reports.