![]() Join cteMode C on A.GrpByYear=C.GrpByYear Join cteMedian B on A.GrpByYear=B.GrpByYear ,DistinctCount = Count(Distinct A.Measure) ![]() ,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) ![]() ,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)) Select RowNr=Row_Number() over (Partition By Year(TR_Date) Order By Year(TR_Date),TR_Y10)įrom. Where I have "GrpByYear", this would have to be expanded into your compound fields. I am calculated only one measure, but I suspect it can easily be expanded cteMode would calculate the mode of cteBase.cteMedian would generate the median of cteBase.cteBase would be your core data (non-aggregated or groupded).Not sure if this will help, but here is some sql which allows me generate some stats (., mean, median, mode.) within a group by TD ON TD.TimeDimension_ID = DV.DataValues_TimeDimension_IDĪND DataValues_DataRowLifecyclePhaseID IN (1, 4) CDS ON CDS.Commodity_ID = DV.DataValues_Commodity_ID (MAX(dv.DataValues_AttributeValue) - MIN(dv.DataValues_AttributeValue)) AS 'Range' WHERE DataValues_AttributeValue IS Not NULL AND DataValues_ERSCommodity_ID = 157 and DataValues_DataRowLifecyclePhaseID = 1 ,(SELECT TOP 1 with ties DataValues_AttributeValue ((MAX(dv.DataValues_AttributeValue) + MIN(dv.DataValues_AttributeValue)) / 2) AS 'Median', MIN(dv.DataValues_AttributeValue) AS 'Minimum value for the Year', MAX(dv.DataValues_AttributeValue) AS 'Maximum value for the Year', AS 'Description',ĪVG(DV.DataValues_AttributeValue) AS 'Average/Mean', I was able to get the average but median, range and mode I'm getting a wrong one.īelow is my code which I tried for the above concept. I'm trying to get mean, median, mode and range for a set of values in a table. Example problem finding mean, median, mode and range: Find the mean, median, mode and range of the following data set: 9,4,17,4,7,8,14 Finding the mean: First add the numbers up: 9+4+17+4+7+8+14 63 Then divide 63 by the total number of data points, 7, and you get 9. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |