SQL Server Aggregate Functions

Summary: in this tutorial, you will learn about the SQL Server aggregate functions and how to use them to calculate aggregates.

An aggregate function operates on a set of values and returns a single value. In practice, you often use aggregate functions with the GROUP BY clause and HAVING clause to aggregate values within groups.

The following table shows the most commonly used SQL Server aggregate functions:

SQL Server aggregate function syntax

Here’s is the general syntax of an aggregate function:

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

In this syntax:

• First, specify the name of an aggregate function that you want to use such as AVG, SUM, and MAX.
• Second, use DISTINCT to apply aggregate distinct values in a set; or use the ALL option to apply the aggregate function to all values including duplicates.
• Third, specify the expression which can be a column of a table or an expression that consists of multiple columns with arithmetic operators.

SQL Server aggregate function examples

We will use the products table from the sample database for the demonstration.

The AVG function example

The following statement uses the AVG() function to return the average list price of all products in the products table:

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

The following shows the output:

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

Because the list price is in USD, it should have at most two decimal places. Therefore, you need to round the result to a number with two decimal places.

To achieve this, you use the ROUND and CAST functions as demonstrated in the following query:

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

Output:

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

In this query:

• First, the ROUND function returns the rounded average list price.
• Then the CAST function converts the result to a decimal number with two decimal places.

The COUNT function example

The following statement uses the COUNT() function to return the number of products whose price is greater than 500:

SELECT
    COUNT(*) product_count
FROM
    production.products
WHERE
    list_price > 500;Code language: SQL (Structured Query Language) (sql)

Output:

product_count
-------------
213

In this example:

• First, the WHERE clause gets products whose list price is greater than 500.
• Second, the COUNT function returns the number of products with list prices greater than 500.

The MAX function example

The following statement uses the MAX() function to return the highest list price of all products:

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

Output:

max_list_price
--------------
11999.99Code language: CSS (css)

The MIN function example

Similarly, the following statement uses the MIN() function to return the lowest list price of all products:

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

Output:

min_list_price
--------------
89.99Code language: CSS (css)

The SUM function example

To demonstrate the SUM() function, we will use the stocks table from the sample database.

The following statement uses the SUM() function to calculate the total stock by product id in all warehouses:

SELECT 
    product_id, 
    SUM(quantity) stock_count
FROM 
    production.stocks
GROUP BY
    product_id
ORDER BY 
    stock_count DESC;Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

How the statement works:

• First, the GROUP BY clause summarized the rows by product id into groups.
• Second, the SUM() function calculated the sum of quantity for each group.

The STDEV function example

The following statement uses the STDEV() function to calculate the statistical standard deviation of all list prices:

SELECT
    CAST(ROUND(STDEV(list_price),2) as DEC(10,2)) stdev_list_price
FROM
    production.products;Code language: SQL (Structured Query Language) (sql)

Output:

stdev_list_price
----------------
1612.15Code language: CSS (css)

Summary

• Aggregate functions operate on rows and return a single row.
• Use aggregate functions with the GROUP BY clause to aggregate values within groups.
• Use the AVG() function to calculate the average of values.
• Use the SUM() function to calculate the total of values.
• Use the COUNT() function to count the number of values in a column.
• Use the MIN() function to get the minimum value in a set.
• Use the MAX() function to get the maximum value in a set.