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

...

...

...

...

...

...

...





Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net