- Function Determinism
- Types of Built-in Functions
- Aggregate Functions
- Statistical Aggregate Functions
- Other Aggregate Functions
- Using Aggregate Functions with COMPUTE
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Statistical Aggregate Functions
The AVG function returns the average of the supplied values. Unlike MIN and MAX, AVG can only accept a numeric expression as a parameter. The following example returns the average quantity of sales:
SELECT
AVG(qty)
FROM sales
Results:
----------- 24
You can specify the DISTINCT keyword if you want to take into account only the unique occurrence of each value:
SELECT
AVG(DISTINCT qty)
FROM sales
Results:
----------- 30
Also note that AVG returns the same (or a similar) data type as the group of values it accepts. So if you pass an integer, expect an integer back; SQL Server does not return decimal places, even if your average is not a whole number. For example, the following query should return 1.5 because that is the average of 1 and 2. However, because the "value" column was declared as INT, the returned result is 1:
DECLARE @average TABLE(
value INT)
INSERT @average
VALUES (1)
INSERT @average
VALUES (2)
SELECT AVG(value) FROM @average
Results:
----------- 1
The SUM function works similarly to AVG, returning the sum of all or unique values. Here are examples of both:
SELECT
SUM(qty) AS total_qty,
SUM(DISTINCT qty) AS sum_of_unique_values
FROM sales
Results:
|
total_qty |
sum_of_unique_values |
|
548 |
363 |
The STDEV and STDEVP functions calculate the standard deviation. If you haven't guessed already, these functions are used mainly by statistical applications. The difference between the two functions is that STDEVP calculates standard deviation for the population of all values within the group. Here is an example:
SELECT
STDEV(qty) AS standard_dev,
STDEVP(qty) AS standard_dev_p
FROM sales
Results:
|
standard_dev |
standard_dev_p |
|
17.3669338 |
16.96764057 |
Similar to the prior two functions, the VAR and VARP functions calculate the statistical variance and statistical variance of the population, respectively, as follows:
SELECT
VAR(qty) AS variance,
VARP(qty) AS variance_p
FROM sales
Results:
|
variance |
variance_p |
|
301.6103896 |
287.9008264 |
AVG, SUM, STDEV, STDEVP, VAR and VARP functions cannot operate on BIT data types; they can operate on all other numeric data types.