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):
Now, here's a list of statistical functions (as well as some standard arithmetical functions that are useful in calculating statistics) and their meanings.
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
SUM(CASE WHEN b.quantity <= a.quantity
THEN 1 ELSE 0 END) >=(COUNT(*))/2
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!