## 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 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)`

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`

if you want only distinct values are considered in the calculation or`ALL`

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;

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;

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;

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;

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;

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;

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;

In this tutorial, you have learned about the SQL Server aggregate functions and how to use them to calculate aggregates.