Summary: in this tutorial, you will learn about the SQL Server aggregate attributes and also exactly how to usage them to calculate aggreentrances.

You are watching: Which of the following is not an aggregate function

An accumulation attribute performs a calculation one or even more values and also returns a solitary value. The accumulation feature is regularly supplied with the GROUP BY clausage and also HAVING clausage of the SELECT statement.

The following table reflects the SQL Server aggregate functions:

Aggregate functionDescription
AVGThe AVG() accumulation feature calculates the average of non-NULL worths in a collection.
CHECKSUM_AGGThe CHECKSUM_AGG() function calculates a checksum value based upon a group of rows.
COUNTThe COUNT() aggregate attribute retransforms the variety of rows in a group, including rows through NULL worths.
COUNT_BIGThe COUNT_BIG() aggregate function retransforms the variety of rows (with BIGINT information type) in a team, consisting of rows with NULL worths.
MAXThe MAX() aggregate feature returns the highest worth (maximum) in a set of non-NULL worths.
MINThe MIN() accumulation feature returns the lowest worth (minimum) in a set of non-NULL worths.
STDEVThe STDEV() feature returns the statistical typical deviation of all values offered in the expression based upon a sample of the data population.
STDEVPThe STDEVP() feature likewise retransforms the standard deviation for all worths in the provided expression, but does so based upon the entire data population.
SUMThe SUM() aggregate attribute returns the summation of all non-NULL values a set.
VARThe VAR() function returns the statistical variance of values in an expression based upon a sample of the specified populace.
VARPThe VARP() function retransforms the statistical variance of values in an expression but does so based on the whole data population.

SQL Server aggregate feature syntax

The adhering to illustrates the syntaxation of an aggregate function:


Code language: SQL (Structured Query Language) (sql)In this syntax;

First, specify the name of an aggregate attribute that you want to use such as AVG, SUM, and MAX.2nd, usage DISTINCT if you desire just distinctive values are thought about in the calculation or ALL if all values are taken into consideration in the calculation. By default, ALL is supplied if you don’t specify any modifier.Third, the expression deserve to be a column of a table or an expression that consists of multiple columns via arithmetic operators.

SQL Server accumulation attribute examples

We will certainly usage the products table from the sample database for the demonstration.

*

AVG example

The complying with statement use the AVG() feature to return the average list price of all products in the products table:


Code language: SQL (Structured Query Language) (sql)The following mirrors the output:

*

Due to the fact that the list price in USD, it must have actually 2 decimal places at a lot of. Therefore, you should round the result to a number with 2 decimal areas. To do this, you use the ROUND and CAST functions as presented in the complying with query:


Code language: SQL (Structured Query Language) (sql)

*
First, the ROUND function returns the rounded average list price. And then the CAST attribute converts the outcome to a decimal number via two decimal locations.

COUNT example

The complying with statement uses the COUNT() function to return the variety of products whose price is greater than 500:


Code language: SQL (Structured Query Language) (sql)The following mirrors the output:

*

In this example:

2nd, the COUNT feature returns the variety of commodities with list prices better than 500.

MAX example

The adhering to statement offers the MAX() function to rerotate the greatest list price of all products:


Code language: SQL (Structured Query Language) (sql)The following photo shows the output:

*

MIN example

Similarly, the complying with statement provides the MIN() function to return the lowest list price of all products:


Code language: SQL (Structured Query Language) (sql)The output is:

*

SUM example

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

*

The complying with statement uses the SUM() feature to calculate the full stock by product id in all warehouses:


SELECT product_id, SUM(quantity) stock_countFROM production.stocksGROUP BY product_idORDER BY stock_count DESC;
Code language: SQL (Structured Query Language) (sql)Here is the output:

*

Here is exactly how the statement works:

Second, the SUM() attribute calculated the amount of amount for each team.

See more: Why Do Managers Consider Direct Costs To Be More Accurate Than Indirect Costs?

STDEV example

The adhering to statement uses the STDEV() attribute to calculate the statistical typical deviation of all list prices:


Code language: SQL (Structured Query Language) (sql)In this tutorial, you have actually learned around the SQL Server accumulation features and also how to usage them to calculate aggregateways.