asked on

Calculate median using SQL

I am trying to calculate the median using the following formula which works. What I would like to do is replace the ' / 2 ' with ' * 0.5 ' so that I can then calculate percentiles rather than just the median.

Problem is that no value is reported at all when I multiple by .5 rather than divide by 2. The value returned is already a double so I'm not sure what the problem is.

Any help would be appreciated.

SELECT      x.LPR AS median, COUNT(y.LPR) AS N
FROM          dbo.UpdatedAnalysis_filtered2 x CROSS JOIN
                        dbo.UpdatedAnalysis_filtered2 y
GROUP BY x.LPR
HAVING       (SUM(CASE WHEN y.LPR <= x.LPR THEN 1 ELSE 0 END) >= (COUNT(*) + 1) /2) AND (SUM(CASE WHEN y.LPR >= x.LPR THEN 1 ELSE 0 END) 
                        >= COUNT(*) /2 + 1)

Open in new window

tigin44

SELECT x.LPR AS median, COUNT(y.LPR) AS N
FROM dbo.UpdatedAnalysis_filtered2 x CROSS JOIN
dbo.UpdatedAnalysis_filtered2 y
GROUP BY x.LPR
HAVING (SUM(CASE WHEN y.LPR <= x.LPR THEN 1 ELSE 0 END) >= ((COUNT(*) + 1)) * 0.5)
AND (SUM(CASE WHEN y.LPR >= x.LPR THEN 1 ELSE 0 END) >= (COUNT(*) *0.5) + 1)

tigin44

this will also give you the mode

SELECT LPR  AS median, A.TotalCount  AS N
FROM
      (
       SELECT LPR, COUNT(QTY) AS TotalCount
       FROM dbo.UpdatedAnalysis_filtered2
       GROUP BY LPR
      ) A
      INNER JOIN
      (SELECT LPR, MAX(TotalCount) AS TotalCount
       FROM (
                  SELECT LPR, COUNT(QTY) AS TotalCount
                  FROM dbo.UpdatedAnalysis_filtered2
                  GROUP BY LPR
               ) A
       GROUP BY LPR
       ) C ON A.LPR = C.LPR AND A.TotalCount = C.TotalCount

Open in new window

pansophy

ASKER

Apparently dividing by 2 rounds down where as multiplying by 0.5 gives a decimal. So the answer is to round down using floor...

SELECT x.LPR AS median, COUNT(y.LPR) AS N
FROM dbo.UpdatedAnalysis_filtered2 x CROSS JOIN
dbo.UpdatedAnalysis_filtered2 y
GROUP BY x.LPR
HAVING (SUM(CASE WHEN y.LPR <= x.LPR THEN 1 ELSE 0 END) >=floor((COUNT(*) + 1)) * 0.5)
AND (SUM(CASE WHEN y.LPR >= x.LPR THEN 1 ELSE 0 END) >= floor((COUNT(*) *0.5) + 1))

Sharath S

check this one

SELECT x.LPR AS median, COUNT(y.LPR) AS N
  FROM dbo.UpdatedAnalysis_filtered2 x 
 CROSS JOIN dbo.UpdatedAnalysis_filtered2 y
 GROUP BY x.LPR
HAVING (SUM(CASE WHEN y.LPR <= x.LPR THEN 1 ELSE 0 END) >= FLOOR(CASE WHEN COUNT(*)%0.5 = 0 THEN (COUNT(*)-1)*0.5 ELSE COUNT(*)*0.5 END)+1) 
   AND (SUM(CASE WHEN y.LPR >= x.LPR THEN 1 ELSE 0 END) >= FLOOR(CASE WHEN COUNT(*)%0.5 = 0 THEN (COUNT(*)-1)*0.5 ELSE COUNT(*)*0.5 END)+1)

Open in new window