Gowtham Ramamoorthy Gowtham Ramamoorthy - 1 year ago 58
SQL Question

How to get mean ,median , mode and range in a singel select querey?

I'm trying to get mean,median,mode and range for a set of values in a table. I was able to get the average but median,range and mode i'm getting a wrong one.

Below is my code which I tried for the above concept.

Select CDS.[Commodity_SourceSeriesID_LongDesc] AS 'Description'
,TD.TimeDimension_Year AS 'Year'
,AVG(DV.DataValues_AttributeValue) AS 'Average/Mean'
,MAX(dv.DataValues_AttributeValue) AS 'Maximum value for the Year'
,MIN(dv.DataValues_AttributeValue) AS 'Minimum value for the Year'
,((MAX(dv.DataValues_AttributeValue) + MIN(dv.DataValues_AttributeValue)) / 2) AS 'Median'
--,(SELECT TOP 1 with ties DataValues_AttributeValue
--FROM [CoSD].[DataValues]
--WHERE DataValues_AttributeValue IS Not NULL AND DataValues_ERSCommodity_ID = 157 and DataValues_DataRowLifecyclePhaseID = 1
--GROUP BY DataValues_AttributeValue
,(MAX(dv.DataValues_AttributeValue) - MIN(dv.DataValues_AttributeValue)) AS 'Range'

FROM [CoSD].[DataValues] DV INNER JOIN [CoSD].[CommodityDataSeries] CDS ON CDS.Commodity_ID = DV.DataValues_Commodity_ID
INNER JOIN [CoSD].[TimeDimension_LU] TD ON TD.TimeDimension_ID = DV.DataValues_TimeDimension_ID
WHERE DataValues_Commodity_ID = 157 and DataValues_DataRowLifecyclePhaseID IN (1,4)

Is there a way to achieve this one ?


Answer Source

Not sure if this will help, but here is some sql which allows me generate some stats (..., mean, median, mode,..) within a group by

  • cteBase would be your core data (non-aggregated or groupded)
  • cteMedian would generate the median of cteBase
  • cteMode would calculate the mode of cteBase

I am calculated only one measure, but I suspect it can easily be expanded Where I have "GrpByYear", this would have to be expanded into your compound fields.

;with cteBase as (
     Select RowNr=Row_Number() over (Partition By Year(TR_Date) Order By Year(TR_Date),TR_Y10)
           ,GrpByYear = Year(TR_Date)
           ,Measure = TR_Y10
     From [Chinrus-Series].[dbo].[DS_Treasury_Rates]
     Where Year(TR_Date)>2014
    ,cteMedian as (Select A.GrpByYear,Measure From cteBase A Join (Select GrpByYear,RowNr=Max(RowNr)/2 from cteBase Group by GrpByYear) B on (A.GrpByYear=B.GrpByYear and A.RowNr=B.RowNr))
    ,cteMode   as (Select * from (Select RowNr=Row_Number() over (Partition By GrpByYear Order by Count(*) Desc),GrpByYear,Measure,Hits=count(*) From cteBase Group by GrpByYear,Measure) A Where RowNr=1)
    Select A.GrpByYear
          ,RecordCount   = Count(*)
          ,DistinctCount = Count(Distinct A.Measure)
          ,SumTotal      = Sum(A.Measure)
          ,Minimum       = Min(A.Measure)
          ,Maximum       = Max(A.Measure)
          ,Mean          = Avg(A.Measure)
          ,Median        = Max(B.Measure)
          ,Mode          = Max(C.Measure)
          ,StdDev        = STDEV(A.Measure)
     From cteBase A
     Join cteMedian B on A.GrpByYear=B.GrpByYear
     Join cteMode   C on A.GrpByYear=C.GrpByYear
     Group By A.GrpByYear
     Order By A.GrpByYear

Year    RecordCount DistinctCount   SumTotal    Minimum Maximum Mean    Median  Mode    StdDev
2016    110         43              204.82      1.63    2.25    1.862   1.84    1.83    0.128568690811108
2015    251         69              536.71      1.68    2.50    2.1382  2.16    2.20    0.1662836533952