Software developer

Check these SQL functions:

• AVG
• STDDEV
• VARIANCE

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:

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.

Reference : SQL Mean & Median