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
--WHERE DataValues_AttributeValue IS Not NULL AND DataValues_ERSCommodity_ID = 157 and DataValues_DataRowLifecyclePhaseID = 1
--GROUP BY DataValues_AttributeValue
--ORDER BY COUNT(*) DESC) AS Mode
,(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)
Not sure if this will help, but here is some sql which allows me generate some stats (..., mean, median, mode,..) within a group by
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