Adding Mode to this SQL

Question

I have this SQL for MS SQL Server 2008:

WITH CTE AS (
SELECT e_id, 
   scale, 
   ROW_NUMBER() OVER(PARTITION BY e_id ORDER BY scale ASC) AS rn,   
   COUNT(scale) OVER(PARTITION BY e_id) AS cn
FROM waypoint.dbo.ScoreMaster
WHERE scale IS NOT NULL
)
SELECT e_id,

cast(AVG (cast(scale as decimal(5,2))) as decimal(5,3)) as [AVG],

cast (STDEV(cast(scale as decimal(5,1))) as decimal(5,3)) as [STDDEV],   
AVG(CASE WHEN 2 * rn - cn BETWEEN 0 AND 2 THEN 
    scale END) AS FinancialMedian,

MAX(CASE WHEN 2 * rn - cn BETWEEN 0 AND 2 THEN 
    scale END) AS StatisticalMedian
from CTE
GROUP BY e_id

and I would like to add the Mode. I've been trying different ideas, but nothing works yet...

Answer 1

Take a look at the following:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/calculating-mean-median-and-mode-with-sq

Answer 2

Try this :

; WITH Mean AS (
SELECT AVG(scale)/1.0 AS MeanVal
  FROM waypoint.dbo.ScoreMaster
 WHERE scale IS NOT NULL
), Median AS (
SELECT ( ( SELECT TOP 1 scale
             FROM ( SELECT TOP 50 PERCENT scale FROM waypoint.dbo.ScoreMaster WHERE scale IS NOT NULL ORDER BY scale ) AS A
            ORDER BY scale DESC )
       + ( SELECT TOP 1 scale
             FROM ( SELECT TOP 50 PERCENT scale FROM waypoint.dbo.ScoreMaster WHERE scale IS NOT NULL ORDER BY scale DESC ) AS A
            ORDER BY scale ASC ) ) / 2.0 AS MedianVal
), Mode AS (
SELECT scale AS ModeVal
  FROM waypoint.dbo.ScoreMaster
 WHERE scale IS NOT NULL
 GROUP BY scale
HAVING COUNT(scale) = ( SELECT TOP 1 COUNT(scale)
                          FROM waypoint.dbo.ScoreMaster
                         WHERE scale IS NOT NULL
                         GROUP BY scale
                         ORDER BY COUNT(scale) DESC )
) 
SELECT MeanVal
     , MedianVal
     , ModeVal
  FROM Mean
     , Median
     , Mode

I know it's ugly, but the trick is to cross join all the possible values given by Mode with the value of Mean and Median.

Answer 3

Or this :

; WITH Mean AS (
SELECT AVG(scale)/1.0 AS MeanVal
  FROM waypoint.dbo.ScoreMaster
 WHERE scale IS NOT NULL
), Median AS (
SELECT ( ( SELECT TOP 1 scale
             FROM ( SELECT TOP 50 PERCENT scale FROM waypoint.dbo.ScoreMaster WHERE scale IS NOT NULL ORDER BY scale ) AS A
            ORDER BY scale DESC )
       + ( SELECT TOP 1 scale
             FROM ( SELECT TOP 50 PERCENT scale FROM waypoint.dbo.ScoreMaster WHERE scale IS NOT NULL ORDER BY scale DESC ) AS A
            ORDER BY scale ASC ) ) / 2.0 AS MedianVal
), Mode AS (
SELECT AVG(scale) AS ModeVal
  FROM waypoint.dbo.ScoreMaster
 WHERE scale IS NOT NULL
HAVING COUNT(scale) = ( SELECT TOP 1 COUNT(scale)
                          FROM waypoint.dbo.ScoreMaster
                         WHERE scale IS NOT NULL
                         GROUP BY scale
                         ORDER BY COUNT(scale) DESC )
) 
SELECT MeanVal
     , MedianVal
     , ModeVal
  FROM Mean
     , Median
     , Mode

if you want Mode to be the AVG between scales with the highest count.

Answer 4

WITH    s (e_id, scale) AS
        (
        SELECT  1, 0.0
        UNION ALL
        SELECT  1, 0.1
        UNION ALL
        SELECT  1, 0.1
        UNION ALL
        SELECT  1, 0.2
        UNION ALL
        SELECT  1, 0.2
        UNION ALL
        SELECT  1, 0.3
        ),
        cte AS
        (
        SELECT  e_id, 
                scale, 
                ROW_NUMBER() OVER (PARTITION BY e_id ORDER BY scale ASC) AS rn,
                COUNT(scale) OVER (PARTITION BY e_id) AS cn,
                COUNT(*) OVER (PARTITION BY e_id, scale) AS sn
        FROM    s
        WHERE   scale IS NOT NULL
        )
SELECT  e_id,
        AVG(scale),
        (
        SELECT  AVG(scale)
        FROM    cte ci
        WHERE   ci.e_id = co.e_id
                AND ci.sn = MAX(co.sn)
        ) AS mode
FROM    cte co
GROUP BY
        e_id

This will select the average of all modal scales. Replace AVG with TOP 1 ... ORDER BY to get a single scale.

Note how we can use MAX(co.sn) in the WHERE clause :)