Computing Statistical Values

Counting and summing are not the only aggregate options that you have available. You can also use both basic and advanced statistical functions in your queries. As before, these functions allow you to work directly with the database to obtain results, yielding excellent processing efficiency.

You can use these functions in the same way that you used the SUM function. Filters and grouping can be added to suit your needs. When you filter or group these functions, the resulting value of each function will vary according to its definition. (The scripts in this section are included in the samples as StatisticalExamplesFromText.sql in the \SqlScripts folder.)

Using AVG

The AVG function returns the average of the values contained in the column used as the argument. The AVG function ignores NULL values, so the presence of NULLs in your data will not change the meaning of the average value returned.

AVG accepts any expression that returns a numeric value as an argument. The argument can be the name of a column or a calculation. However, sub-queries or other aggregate functions are not allowed as arguments.

This is a simple SELECT statement that yields the average selling price for each product:

SELECT Production.Product.Name AS Product,     AVG(Sales.SalesOrderDetail.UnitPrice) AS [Avg Price] FROM Sales.SalesOrderDetail     INNER JOIN         Production.Product         ON         Sales.SalesOrderDetail.ProductID = Production.Product.ProductID GROUP BY Production.Product.Name ORDER BY Production.Product.Name

Here we have another SELECT statement, yielding the average prices for each product by month and the general average by product.

SELECT     CASE GROUPING(Production. Product .Name)         WHEN 1 THEN 'Global Average'         ELSE             Production. Product .Name     END AS Product,     CASE GROUPING(         CONVERT(nvarchar(7), Sales.SalesOrderHeader.OrderDate, 111))         WHEN 1 THEN 'Average'         ELSE             CONVERT(nvarchar(7),Sales.SalesOrderHeader.OrderDate, 111)     END AS Period,     AVG(Sales.SalesOrderDetail.UnitPrice) AS [Avg Price] FROM Sales.SalesOrderDetail     INNER JOIN Production.Product         ON Sales.SalesOrderDetail.ProductID =             Production.Product.ProductID     INNER JOIN Sales.SalesOrderHeader         ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID GROUP BY     Production.Product.Name,     CONVERT(nvarchar(7), Sales.SalesOrderHeader.OrderDate, 111)         WITH ROLLUP ORDER BY     GROUPING(Production.Product.Name),     Production.Product.Name,     Period

Using MIN and MAX

These two functions return the minimum or maximum value from an expression. Once again, the expression used as an argument for these functions can be a column name or other calculation, and you can use GROUP or ROLLUP to format the results. Execute the following script to see the MIN and MAX functions in action:

SELECT Production.Product.Name,     MIN(Sales.SalesOrderDetail.UnitPrice) AS [Min Price],     MAX(Sales.SalesOrderDetail.UnitPrice) AS [Max Price],     AVG(Sales.SalesOrderDetail.UnitPrice) AS [Avg Price] FROM Sales.SalesOrderDetail INNER JOIN Production.Product     ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID GROUP BY Production.Product.Name ORDER BY Production.Product.Name

Using Complex Statistical Functions

Sometimes, it is necessary to analyze the data in a database more deeply than the functions we have studied so far allow. For instance, demographic analysis and population estimation, used mostly in social or medical applications, require a more in-depth analysis of the collected data. Some of the more complex statistical functions you can use for further analysis of data are STDEV, STDEVP, VAR and, VARP.

Note

These are very specialized functions. If you're not sure how to apply them, for now just be aware that the functions are available.

Using VAR

The VAR function returns the statistical variance for the numeric values in the specified expression.

Using VARP

This function returns the statistical variance for the population for all values in the specified expression.

Using STDEV and STDEVP

STDEV returns the statistical standard deviation of the values in a specified expression, while STDEVP returns the statistical standard deviation of the population of values in a specified expression.

Please, refer to statistical documentation for more information about the meaning of these statistical functions.

Using the DISTINCT Keyword

All of the aggregate functions accept the DISTINCT modifier, which causes the aggregate calculation to be performed only on each unique occurrence of a value.

SELECT Production.Product.Name,     MIN(DISTINCT Sales.SalesOrderDetail.UnitPrice) AS [Min Price],     MAX(DISTINCT Sales.SalesOrderDetail.UnitPrice) AS [Max Price],     AVG(DISTINCT Sales.SalesOrderDetail.UnitPrice) AS [Avg Price] FROM Sales.SalesOrderDetail INNER JOIN Production.Product     ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID GROUP BY Production.Product.Name ORDER BY Production.Product.Name

In the following table, you can compare the results from the above script run with and without the DISTINCT keyword. Notice that the MIN and MAX values do not change, but the AVG is different in most cases.

Table 5-8. Results With and Without the Distinct Keyword

	With DISTINCT			Without DISTINCT
	Min	Max	AVG	Min	Max	AVG
All-Purpose Bike Stand	$ 159.00	$ 159.00	$ 159.00	$ 159.00	$ 159.00	$ 159.00
AWC Logo Cap	4.32	8.99	5.37	4.32	8.99	7.67
Bike Wash - Dissolver	3.98	7.95	5.14	3.98	7.95	6.94
Cable Lock	14.50	15.00	14.75	14.50	15.00	14.99
Chain	11.74	12.14	11.94	11.74	12.14	12.14
Classic Vest, L	38.10	63.50	50.80	38.10	63.50	62.74
Classic Vest, M	34.93	63.50	43.34	34.93	63.50	47.06
...	...	...	...	...	...	...