Aggregate Functions

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 the expression based on a sample of the data

population.


STDEVP The STDEVP() function also returns the standard deviation for all

values in the provided expression, 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 does so 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.


Source: Guru99

Recent Posts

See All