Practical Mean

In statistics, mean has two related meanings:
- the arithmetic mean.
- the expected value of a random variable, which is also called the population mean.

To get the mean for a column in database, in general built-in aggregation function AVG() is good enough. However, one much understand this function is of arithmetic mean. If your database contains noise data, this is not good enough, simply because this mean does not have noise data prune mechanism. While different means would have their precise meaning, if you do not know which to use, use interquartile mean (IQM).

Following is the some notes related to T-SQL’s mean functions:

Arithmetic mean (AM)
There is built-in function – Never use DISTINCT, and no need to use ALL because it is default:

AVG ( [ ALL | DISTINCT ] expression )

Median
Median is not mean, but it is included here anyway. There is no such function in T-SQL. But you can get from here (credits to Itzik Ben-Gan):

SELECT(
(SELECT MAX(Value) FROM
(SELECT TOP 50 PERCENT Value FROM dbo.VOrders ORDER BY Value) AS H1)
+
(SELECT MIN(Value) FROM
(SELECT TOP 50 PERCENT Value FROM dbo.VOrders ORDER BY Value DESC) AS H2)
) / 2 AS Median;

Interquartile mean (IQM)
There is no such function in T-SQL. But you can get from here:

SELECT AVG(S1_2) AS P1_2
FROM Minut
WHERE S1_2 IS NOT NULL
AND Minut.Trip_Id NOT IN
(SELECT TOP 25 PERCENT Trip_Id FROM Minut WHERE S1_2 IS NOT NULL ORDER BY S1_2)
AND Minut.Trip_Id NOT IN
(SELECT TOP 25 PERCENT Trip_Id FROM Minut WHERE S1_2 IS NOT NULL ORDER BY S1_2 DESC)

If the dataset is so small, such as only a few records exist, SELECT TOP 25 PERCENT won't work. Instead, use following solution:

SELECT AVG(S1_2) AS P1_2
FROM Minut
WHERE S1_2 IS NOT NULL
AND Minut.Trip_Id NOT IN
(
SELECT TOP
(
SELECT CONVERT(INT, COUNT(*)/4)
FROM Minut
WHERE S1_2 IS NOT NULL
) Trip_Id
FROM Minut
WHERE S1_2 IS NOT NULL
ORDER BY S1_2
)
AND Minut.Trip_Id NOT IN
(
SELECT TOP
(
SELECT CONVERT(INT, COUNT(*)/4)
FROM Minut
WHERE S1_2 IS NOT NULL
) Trip_Id
FROM Minut
WHERE S1_2 IS NOT NULL
ORDER BY S1_2 DESC
)


http://en.wikipedia.org/wiki/Mean
http://blogical.se/blogs/mikael_sand/archive/2009/07/24/t-sql-is-mean-there-is-no-median.aspx
http://www.sqlmag.com/article/tsql3/calculating-the-median-gets-simpler-in-sql-server-2005.aspx
http://koncordpartners.blogspot.com/2010/12/rounding-issue-in-select-top-n-percent.html

Percentage Format in T-SQL

There is no function to present the percentage format in T-SQL, something like 17.25%. There are various ways to do this. However, for reporting purpose, the easiest way might be:

CONVERT(VARCHAR, Result_From_Calc)+'%'

Following is an example how to add Total row at bottom of the selected dataset as will as add percentage column for each of rows.

The original dataset is:

SELECT Leg_Id
, Statute_Miles
FROM dw_Legs
GO

The final script is as follows:

WITH Totaling
AS
(
SELECT SUM(Statute_Miles) AS Milage
FROM dw_Legs
)
SELECT Leg
, Milage
, CONVERT(VARCHAR, Milage*100/(SELECT Milage FROM Totaling))+'%' AS Percentage
FROM
(
SELECT CONVERT(VARCHAR, Leg_Id) AS Leg
, Statute_Miles AS Milage
FROM dw_Legs
UNION
SELECT 'Total' AS Leg
, Milage
FROM Totaling
) AS Dataset
GO

Explaination:

1. Total milage will be used more than once, so it has been in put into WITH clause.
2. The reason convert Leg_Id into VARCHAR is because word ‘Total’ is a VARHCAR. Else the UNION won’t work.
3. Make sure there is only one row being selected into WITH clause. That is where people usually create a bug.

Following is the result: