Gowtham Ramamoorthy - 9 months ago 21

SQL Question

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

--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)

GROUP BY

DV.DataValues_TimeDimension_ID,

CDS.Commodity_SourceSeriesID_LongDesc,

TD.TimeDimension_Year

Is there a way to achieve this one ?

Thanks

Answer

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
```