Home > Articles > Data > SQL Server

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account