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.