AVG() Function in SQL Server
Last Updated :
11 Sep, 2024
The AVG()
function in SQL Server is an essential aggregate function used to compute the average value of a numeric column.
It works by summing all non-NULL values in the specified column and then dividing the total by the number of these values. In this article, We will learn about AVG() Function in SQL Server by understanding various examples in detail.
AVG() Function in SQL Server
- The
AVG(
)
function in SQL Server is an aggregate function used to calculate the average value of a numeric column.
- It sums up all the values in the column and divides the total by the number of non-NULL values.
- The
AVG()
function ignores NULL values.
Features:
- This function comes under Numeric Functions.
- This function accepts only one parameter, namely expression.
- This function ignores NULL values.
Syntax:
AVG(expression)
Parameter:
This method accepts one parameter.
- expression – A specified numeric value may be either a stated field or a stated formula.
Returns:
It returns the average value of the specified expression.
Examples of AVG() Function in SQL Server
For better understanding of AVG() Function in SQL Server we will use the table called Sales which is shown below:
SaleID |
Product |
Quantity |
Price |
1 |
Laptop |
2 |
1000 |
2 |
Smartphone |
5 |
500 |
3 |
Tablet |
3 |
700 |
4 |
Laptop |
1 |
1100 |
5 |
Smartphone |
4 |
450 |
6 |
Laptop |
2 |
NULL |
Example 1: Basic SQL Server AVG()
Function
Calculate the average price of all products in the Sales
table, including non-NULL values only.
Query:
SELECT AVG(Price) AS AveragePrice
FROM Sales;
Output:
Explanation:
This query calculates the average price of products in the Sales
table. It ignores the row where the Price
is NULL, averaging the prices 1000, 500, 700, 1100, and 450.
Example 2: Using SQL Server AVG()
with GROUP BY
Calculate the average price for each product in the Sales
table.
Query:
SELECT Product, AVG(Price) AS AveragePrice
FROM Sales
GROUP BY Product;
Output:
Product |
AveragePrice |
Laptop |
1050.00 |
Smartphone |
475.00 |
Tablet |
700.00 |
Explanation:
This query groups the data by product type and calculates the average price for each group.
Example 3: Using SQL Server AVG()
in HAVING
Clause
Find the products with an average price greater than 500.
Query:
SELECT Product, AVG(Price) AS AveragePrice
FROM Sales
GROUP BY Product
HAVING AVG(Price) > 500;
Output:
Product |
AveragePrice |
Laptop |
1050.00 |
Tablet |
700.00 |
Explanation:
This query uses the HAVING
clause to filter groups where the average price is greater than 500.
Example 4: AVG()
Function with WHERE
Clause
Calculate the average price of products where the quantity is greater than 3.
Query:
SELECT AVG(Price) AS AveragePrice
FROM Sales
WHERE Quantity > 3;
Output:
Explanation:
This query calculates the average price of products where the Quantity
is greater than 3. Only rows with quantities of 4 and 5 are considered.
Example 5: AVG()
Function with ORDER BY
Clause
Display the products and their average price, ordered by the average price in ascending order.
Query:
SELECT Product, AVG(Price) AS AveragePrice
FROM Sales
GROUP BY Product
ORDER BY AVG(Price);
Output:
Product |
AveragePrice |
Smartphone |
475.00 |
Tablet |
700.00 |
Laptop |
1050.00 |
Explanation:
This query groups by product and orders the results by average price in ascending order.
Example 6: AVG()
Function with DISTINCT
Clause
Calculate the average price of distinct prices in the Sales
table.
Query:
SELECT AVG(DISTINCT Price) AS AveragePrice
FROM Sales;
Output:
Explanation:
This query calculates the average of distinct price values. In this case, only unique prices (1000, 500, 700, 1100, and 450) are considered for the average.
Conclusion
The AVG()
function in SQL Server provides a straightforward method for calculating averages, whether you are analyzing individual columns or grouped data. By utilizing additional clauses like GROUP BY
, HAVING
, WHERE
, and ORDER BY
, you can perform more detailed and specific analyses. Understanding and leveraging the AVG()
function can significantly enhance data reporting and decision-making processes.
Similar Reads
DAY() Function in SQL Server
DAY() function : This function in SQL Server is used to return the day of the month i.e, from 1st to 31st for date stated. Features : This function is used to find the day of the month for a date specified. This function comes under Date Functions. This function accepts only one parameter i.e, date.
2 min read
CHAR() function in SQL Server
CHAR() : This function helps to convert an int ASCII code to character value i.e if the user pass an integer as an argument, the CHAR() function interpret the integer value and returns its corresponding character value. Syntax : CHAR(integer_value) Parameters : This function accepts only one argumen
2 min read
ACOS() Function in SQL Server
ACOS() function : This function in SQL Server is used to return the arc cosine or the inverse cosine of a specified value. Input to the arc-cosine function should must be in between -1 and 1 (inclusive), otherwise this function returns NULL. Features : This function is used to find the arc cosine or
2 min read
ATAN() Function in SQL Server
ATAN() function : This function in SQL Server is used to return the arc tangent or the inverse tangent of a specified value. This function accepts only one parameter which is a number and the range accepted for the argument number is unbounded. This function returns a value in the range of -pi/2 to
2 min read
ATN2() Function in SQL Server
In this article, we are going to cover the ATN2()function in which we will see how we can get the arc tangent of two given numbers. Let's discuss one by one. // here val1 and val2 are input. Input : ATN2(val1, val2) Output : Arc tangent result. ATN2() : It is the function that returns the arc tangen
1 min read
ASIN() Function in SQL Server
ASIN() function : This function in SQL Server is used to return the arc sine or the inverse sine of a specified value. Input to the arc-sine function should must be in between -1 and 1 (inclusive), otherwise this function returns NULL. Features : This function is used to find the arc sin or the inve
2 min read
ASCII() Function in SQL Server
The ASCII() function returns the ASCII value of the leftmost character of a character expression. Syntax : ASCII(character_expression) Parameter : This method accepts a single-parameter as mentioned above and described below : character_expression : It can be a literal character, an expression of a
2 min read
EXP() Function in SQL Server
EXP() function : This function in SQL Server is used to return a value which is e raised to the nth power, where n is a specified number. Here âeâ is a mathematical constant and the base of natural logarithms whose value is 2.7182818 and n is the input number taken as the parameter of the function.
2 min read
CONCAT() function in SQL Server
CONCAT() : This function in SQL Server helps to concatenate two or more strings together. CONCAT() function can accept a minimum of 2 parameters and a maximum of 254 parameters. Syntax : CONCAT(string_1, string_2, .......string_n) Parameters : string_1, string_2, .......string_n - The given strings
2 min read
PI() Function in SQL Server
PI() function : This function in SQL Server is used to return the constant float value of math Pi. The default number of decimal places displayed is seven, but SQL Server uses the full double-precision value internally. Features : This function is used to get the value of pi.This function does not a
1 min read