Build upon SQL's built-in statistical functions

by Arthur Fuller | Jul 26, 2005 7:00:00 AM

Tags: Databases, Arthur Fuller, SQL, statistical function, deviation, standard deviation, SQL Server Newsletter

Takeaway: Arthur Fuller defines statistical terms and functions and then demonstrates how you can benefit from using SQL's built-in statistical functions.

Even though SQL wasn't designed for calculating statistics, it does include a number of basic statistical functions. In this article, I'll define statistical terms and functions and then demonstrate how you can benefit from using SQL's built-in statistical functions.

Here are some basic statistics terms (which, if you're not very familiar with statistics, will come in handy when I define statistical functions below):

• Population: the total number of values of interest.
• Sample: When working with large populations, it's often impractical to use the whole population; therefore, statisticians use a sample extracted from the entire population. The sample must be large enough to accurately represent the population.
• Mean: the average, which is calculated by dividing the values' sum by the values' count.
• Mode: the value that occurs most frequently in the set that's being analyzed. One problem with the mean is that none of the values may match it exactly.
• Median: the value that is halfway between the lowest and highest values in the set; that is, the value with an equal number of values above and below it.
• Standard deviation: measures the scatter of values with respect to the mean. When the standard deviation is small, the values lie very close to the mean; the larger it gets, the more values you'll find that are far away from the mean. To put it another way, with a large deviation the bell curve is low and wide; with a small deviation the bell curve is high and narrow.
• Variance: the square of the standard deviation.

Now, here's a list of statistical functions (as well as some standard arithmetical functions that are useful in calculating statistics) and their meanings.

• AVG(): returns the mean
• COUNT(): returns the population (or sample, depending on the row source)
• SQUARE(n): returns the square of the value specified
• POWER(a,n): returns the value of a to the nth power
• SQRT(n): returns the square root of n
• SUM(): returns the sum of the values in a set
• STDDEV(): returns the standard deviation of a sample
• STDDEVP(): returns the standard deviation of a population
• VAR(): returns the variance of a sample
• VARP(): returns the variance of a population

Notice that there aren't any built-in functions for calculating mode and median. Nevertheless, you can derive these values with a little effort.

Using the Northwind sample database, let's suppose that you want to determine the mode of quantities ordered. Here's how you do it:

SELECT TOP 1 quantity, COUNT(*) Count
FROM [order details]
GROUP BY quantity
ORDER BY Count DESC

Calculating the median isn't much more difficult (although there is a wrinkle). The basic strategy is to join the table to itself on the column of interest, using one table to count the rows that are less than the given value, and using the other table to count the rows that are greater than that value. This is the query that calculates the median:

SELECT a.quantity median
FROM [order details] a, [order details] b
GROUP BY a.quantity
HAVING
    SUM(CASE WHEN b.quantity <= a.quantity
       THEN 1 ELSE 0 END) >=(COUNT(*))/2
    AND
    SUM(CASE WHEN b.quantity >= a.quantity
       THEN 1 ELSE 0 END)>=(COUNT(*)/2)

Here's the wrinkle: The code above assumes that the number of rows is odd; therefore, there is guaranteed to be a middle row. When the number of rows is even, you have a choice of which row to return. In my experience, most statisticians return the lower of the two middle values.

I'm not recommending that you write an entire statistics package using SQL. But, if your needs are relatively simple, you can use the built-in functions, build upon them, and derive the basic statistical values.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

Print/View all Posts Comments on this article

Median code not practical MikeSQLDBA | 01/24/06

Hmm Tony Hopkinson | 01/24/06

absolute cursor works too MikeSQLDBA | 01/25/06

Should be faster Tony Hopkinson | 01/25/06