Summary: in this tutorial, you will learn about the SQL Server aggregate functions and how to use them to calculate aggregates.
An aggregate function performs a calculation one or more values and returns a single value. The aggregate function is often used with the GROUP BY
clause and HAVING
clause of the SELECT
statement.
The following table shows the SQL Server aggregate functions:
Aggregate function | Description |
---|---|
AVG | The AVG() aggregate function calculates the average of non-NULL values in a set. |
CHECKSUM_AGG | The CHECKSUM_AGG() function calculates a checksum value based on a group of rows. |
COUNT | The COUNT() aggregate function returns the number of rows in a group, including rows with NULL values. |
COUNT_BIG | The COUNT_BIG() aggregate function returns the number of rows (with BIGINT data type) in a group, including rows with NULL values. |
MAX | The MAX() aggregate function returns the highest value (maximum) in a set of non-NULL values. |
MIN | The MIN() aggregate function returns the lowest value (minimum) in a set of non-NULL values. |
STDEV | The STDEV() function returns the statistical standard deviation of all values provided in theexpression based on a sample of the data population. |
STDEVP | The STDEVP() function also returns the standard deviation for all values in the providedexpression, but does so based on the entire data population. |
SUM | The SUM() aggregate function returns the summation of all non-NULL values a set. |
VAR | The VAR() function returns the statistical variance of values in an expression based on a sample of the specified population. |
VARP | The VARP() function returns the statistical variance of values in an expression but doesso based on the entire data population. |
SQL Server aggregate function syntax
The following illustrates the 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
, andMAX
. - Second, use
DISTINCT
if you want only distinct values are considered in the calculation orALL
if all values are considered in the calculation. By default,ALL
is used if you don’t specify any modifier. - Third, the
expression
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.
AVG example
The following statement use 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:
Because the list price in USD, it should have two decimal places at most. Therefore, you need to round the result to a number with two decimal places. To do this, you use the ROUND
and CAST
functions as shown 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)
First, the ROUND
function returns the rounded average list price. And then the CAST
function converts the result to a decimal number with two decimal places.
COUNT 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)
The following shows the output:
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.
MAX 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)
The following picture shows the output:
MIN 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)
The output is:
SUM 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)
Here is the output:
Here is 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.
STDEV 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)
In this tutorial, you have learned about the SQL Server aggregate functions and how to use them to calculate aggregates.