Question
James Woo · Jan 10, 2019

Software developer

How to calculate median and mean in SQL?

0
0 421
Discussion (6)2
Log in or sign up to continue

That's great, but unfortunately, we've not upgraded to the latest and greatest. We're still using the 2015 release.

Can you ballpark the workload?

If it's not too big, I think the easiest solution would be just writing ObjectScript method that calculates what you need.

Yes, I've been using the AVG function. Any solution will do. Any sample code out there? Thanks

AVG exists in 2015.1.  If you want the median you might have to calculate that yourself within a function.  Do you need an SQL-only solution?

Mean

The mean is calculated by adding all the values in a data set, then dividing by the number of values in the set.

In SQL Server, this can easily be achieved by using the AVG function. (Note that NULL values are ignored by this function.)

Ex:

SELECT SalesPersonID, AVG(Value) AS MeanValue
FROM Sales.OrdersBySalesperson AS OBSP
WHERE SalesPersonID IN (274, 275, 277, 278, 279, 282)
GROUP BY SalesPersonID ORDER BY SalesPersonID;

Results:

SQL Results

Median

The median is calculated by arranging all values in the data set in order, then determining the middle number. If there are an even number of values, you’ll add the two in the middle and calculate the mean. In SQL Server, this isn’t as easy to achieve. However, with the addition of common table expressions (CTEs) and ranking functions, it has become easier.

First, we create a CTE that will order the sales value. The ROW_NUMBER function ranks the orders by value, looking at each salesperson separately. The COUNT function will tell us how many orders the salesperson has.

WITH OrdersBySP (SPID, Value, RowNum, CountOrders) AS 
( 
     SELECT SalesPersonID, 
            Value, 
            ROW_NUMBER() OVER (PARTITION BY SalesPersonID ORDER BY Value), 
            COUNT(SalesOrderID) OVER (PARTITION BY SalesPersonID) 
FROM Sales.OrdersBySalesperson AS OBSP 
WHERE SalesPersonID IN (274, 275, 277, 278, 279, 282) 
) 
SELECT SPID, Value, RowNum, CountOrders FROM OrdersBySP;

Here’s a sample of the results. As you can see, salesperson 275 has a total of 86 orders. Salesperson 277 has 97.

SQL Results

Reference : SQL Mean & Median