SQL Server AVG() Function

Summary: in this tutorial, you will learn how to use the SQL Server AVG() function to calculate the average value from a group of values.

Introduction to SQL Server AVG() function

SQL Server AVG() function is an aggregate function that returns the average value of a group.

The following illustrates the syntax of the AVG() function:

AVG([ALL | DISTINCT] expression)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• ALL instructs the AVG() function to take all values for calculation. ALL is used by default.
• DISTINCT instructs the AVG() function to operate only on unique values.
• expression is a valid expression that returns a numeric value.

The AVG() function ignores NULL values.

SQL Server AVG() function: ALL vs. DISTINCT

The following statements create a new table, insert some values into the table, and query data against it:

CREATE TABLE t(
    val dec(10,2)
);
INSERT INTO t(val) 
VALUES(1),(2),(3),(4),(4),(5),(5),(6);

SELECT val FROM t;Code language: SQL (Structured Query Language) (sql)

Output:

val
----
1.00
2.00
3.00
4.00
4.00
5.00
5.00
6.00
(8 rows)Code language: CSS (css)

The following statement uses the AVG() function to calculate the average of all values in the t table:

SELECT AVG(ALL val) avg 
FROM t;Code language: SQL (Structured Query Language) (sql)

Output:

avg
--------
3.750000
(1 row)Code language: CSS (css)

In this example, we use the ALL modifier, therefore, the average function considers all eight values in the val column in the calculation:

(1 + 2 + 3 + 4 + 4 + 5 + 5 + 6) /  8 = 3.75Code language: SQL (Structured Query Language) (sql)

The following statement uses the AVG() function with DISTINCT modifier:

SELECT AVG(DISTINCT val) avg
FROM t;Code language: SQL (Structured Query Language) (sql)

Here is the result:

avg
--------
3.500000
(1 row)Code language: CSS (css)

Because of the DISTINCT modifier, the AVG() function performs the calculation on distinct values:

(1 + 2 + 3 + 4 + 5 + 6) / 6 = 3.5Code language: SQL (Structured Query Language) (sql)

SQL Server AVG() function examples

Let’s take some examples to see how the AVG() function works.

1) Basic SQL Server AVG() function example

The following example returns the average list price of all products:

SELECT
    AVG(list_price) avg
FROM
    production.products;Code language: SQL (Structured Query Language) (sql)

In this example, the AVG() function returns a single value for the whole table.

Output:

avg
-----------
1520.591401Code language: CSS (css)

To make the average price easier to read, you can round it using the ROUND() function and cast the result to a number with two decimal places:

SELECT
    CAST(ROUND(AVG(list_price),2) AS DEC(10,2)) avg
FROM
    production.products;Code language: SQL (Structured Query Language) (sql)

Output:

avg
-------
1520.59Code language: CSS (css)

2) Using SQL Server AVG() with GROUP BY example

If you use the AVG() function with a GROUP BY clause, the AVG() function returns a single value for each group instead of a single value for the whole table.

The following example uses the AVG() function with the GROUP BY clause to retrieve the average list price for each product category:

SELECT
    category_name,
    CAST(ROUND(AVG(list_price),2) AS DEC(10,2))  avg_product_price
FROM
    production.products p
    INNER JOIN production.categories c 
        ON c.category_id = p.category_id
GROUP BY
    category_name
ORDER BY
    category_name;Code language: SQL (Structured Query Language) (sql)

Output:

category_name       | avg_product_price
--------------------+------------------
Children Bicycles   | 287.79
Comfort Bicycles    | 682.12
Cruisers Bicycles   | 730.41
Cyclocross Bicycles | 2542.79
Electric Bikes      | 3281.66
Mountain Bikes      | 1649.76
Road Bikes          | 3175.36
(7 rows)

3) Using SQL Server AVG() in HAVING clause example

The following example uses the AVG() function in the HAVING clause to retrieve only brands whose average list prices are more than 500:

SELECT
    brand_name,
    CAST(ROUND(AVG(list_price),2) AS DEC(10,2)) avg_product_price
FROM
    production.products p
    INNER JOIN production.brands c ON c.brand_id = p.brand_id
GROUP BY
    brand_name
HAVING
    AVG(list_price) > 500
ORDER BY
    avg_product_price;Code language: SQL (Structured Query Language) (sql)

Output:

brand_name   | avg_product_price
-------------+-------------------
Sun Bicycles | 524.47
Haro         | 621.99
Ritchey      | 749.99
Electra      | 761.01
Surly        | 1331.75
Heller       | 2173.00
Trek         | 2500.06
(7 rows)

In this example:

• First, the GROUP BY clause divides the products by brands into groups.
• Second, the AVG() function calculates the average list price for each group.
• Third, the HAVING clause removes the brand whose average list price is less than 500.

Summary

• Use the AVG() function to calculate the average value from a group of values.
• Use the AVG() function with the GROUP BY clause to calculate the average of each group.