InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 03:16:03 GMT20http://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxOnce I changed your table definition for the Grade column from VARCHAR to NUMERIC(10,2), this works for me:[code="sql"]WITH cte AS( SELECT CourseDate, PersonID, Grade, ROW_NUMBER() OVER(PARTITION BY CourseDate ORDER BY Grade) AS RowNum, COUNT(*) OVER(PARTITION BY CourseDate) As cnt FROM estats)SELECT CourseDate, AVG(Grade) AS MedianFROM cteWHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2)GROUP BY CourseDateORDER BY CourseDate;[/code]It returns medians of 90, 76, and 90.5 for CourseDates of 2010-03-01, 2011-03-02, and 2012-03-01, respectively.I'm not sure why you would define the Grade column to be VARCHAR() when all your data are numerics. I haven't looked into this more, but it may be that this is a source of error. I suspect that you're getting an implicit conversion from VARCHAR() to INT, which leads to loss of necessary accuracy when the final AVG() function is called.RichFri, 15 Feb 2013 11:01:56 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxI LOVE the solutions you all provided! Thanks a ton for the help! I'm learning a lot from playing with what you all wrote in my test environment. Super helpful.Dwain, I'm going to convert yours over to production and see if this will do the trick. I have high hopes. I also think this thread will be useful for anyone else needing to write something for a box plot. I already don't like them :-)Thu, 24 Jan 2013 12:52:03 GMTDataAnalyst011http://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspx[quote][b]dwain.c (1/23/2013)[/b][hr]I don't think the value in bold is correct. If I understand your computation correctly you're trying to find the median of the lower half of the grades for 2011, which would be in this set from your input stream:[code="plain"]114 69.0 2011-03-02115 70.0 2011-03-02116 75.0 2011-03-02118 76.0 2011-03-02113 76.0 2011-03-02[/code]And that value is 116's score of 75.[/quote]I know there are a couple different schools of thought when calculating the quartiles. The way I always learned was that when you have an odd number of values (such as this case for the year 2011), the median is the number in the (n+1)/2 position then you do not include that number when breaking up the dataset. So you would exclude 113's grade in your case since it was selected as the median and then calculate the median of 69, 70, 75, and 75 to get the lower quartile which is 72.5.Thu, 24 Jan 2013 08:07:21 GMTroryp 96873http://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspx[quote][b]roryp 96873 (1/23/2013)[/b][hr]Well, definitely not the prettiest thing I've ever written...[/quote]Are you kidding? Ugly is my middle name... :-P[quote][b]DataAnalyst011 (1/23/2013)[/b][hr][code="plain"]Year Median Max Min LowerQ UpperQ2010 90 99 81 88 922011 76 88 69 [b]72.5[/b] 84.52012 90.5 97 72 86.5 94.5[/code][/quote]I don't think the value in bold is correct. If I understand your computation correctly you're trying to find the median of the lower half of the grades for 2011, which would be in this set from your input stream:[code="plain"]114 69.0 2011-03-02115 70.0 2011-03-02116 75.0 2011-03-02118 76.0 2011-03-02113 76.0 2011-03-02[/code]And that value is 116's score of 75.So here is my solution, my ugly baby. Gotta love it cause it's my baby![code="sql"];WITH [Stats] AS ( SELECT PersonID, Grade, CourseDate, [Year] ,Median=CASE WHEN Count1%2 = 0 AND rn1 IN (Count1/2, (Count1/2)+1) THEN Grade WHEN Count1%2 = 1 AND rn1 = (Count1/2)+1 THEN Grade END -- Used to establish median over quartiles ,rn2=ROW_NUMBER() OVER (PARTITION BY [Year], Quartile ORDER BY Grade) ,Count2=COUNT(*) OVER (PARTITION BY [Year], Quartile) ,Quartile FROM ( SELECT PersonID, Grade=CAST(Grade AS DECIMAL(10,1)), CourseDate, [Year] -- Used to establish median over years ,rn1=ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY Grade) ,Count1=COUNT(*) OVER (PARTITION BY [Year]) -- Break the grades into two groups to establish "median" in a quartile ,Quartile=NTILE(2) OVER (PARTITION BY [Year] ORDER BY Grade) FROM EStats CROSS APPLY (SELECT [Year]=LEFT(CourseDate, 4)) a) a )SELECT [Year] ,Median=AVG(Median) ,[Max]=MAX(Grade) ,[Min]=MIN(Grade) ,LowerQ=AVG(CASE WHEN Quartile = 1 AND Count2%2 = 0 AND rn2 IN (Count2/2, (Count2/2)+1) THEN Grade WHEN Quartile = 1 AND Count2%2 = 1 AND rn2 = (Count2/2)+1 THEN Grade END) ,UpperQ=AVG(CASE WHEN Quartile = 2 AND Count2%2 = 0 AND rn2 IN (Count2/2, (Count2/2)+1) THEN Grade WHEN Quartile = 2 AND Count2%2 = 1 AND rn2 = (Count2/2)+1 THEN Grade END)FROM [Stats]GROUP BY [Year][/code]Let me know if this helps.[b]Edit:[/b] Tidied up my solution a bit.Wed, 23 Jan 2013 18:25:54 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxWell, definitely not the prettiest thing I've ever written, but it at least works with the data you have. I have no idea how it would scale on a much larger dataset. Give it a try and let me know what you think.[code="sql"]with yearCounts as( select DATEPART(year, CourseDate) CourseYear, COUNT(*) GradeCount from EStats group by DATEPART(year, CourseDate)),qOrder as( select *, ROW_NUMBER() over (partition by y.CourseYear order by e.Grade) rn from EStats e join yearCounts y on y.CourseYear = DATEPART(year, e.CourseDate))select q.CourseYear , SUM(case when q.GradeCount % 2 = 1 then case when q.rn = (q.GradeCount+1)/2 then q.Grade else 0 end else case when q.rn in ((q.GradeCount/2),(q.GradeCount/2)+1) then convert(decimal(4,1), q.Grade)/2 else 0 end end) Median , SUM(case when q.rn = q.GradeCount then q.Grade else 0 end) [Max] , SUM(case when q.rn = 1 then q.Grade else 0 end) [Min] , SUM(case when q.GradeCount % 2 = 1 then case when ((q.GradeCount - 1)/2) % 2 = 1 then case when q.rn = (((q.GradeCount-1)/2)+1)/2 then q.Grade else 0 end else case when q.rn in ((((q.GradeCount-1)/2)/2),(((q.GradeCount-1)/2)/2)+1) then convert(decimal(4,1), q.Grade)/2 else 0 end end else case when (q.GradeCount/2) % 2 = 1 then case when q.rn = ((q.GradeCount/2)+1)/2 then q.Grade else 0 end else case when q.rn in (((q.GradeCount/2)/2),((q.GradeCount/2)/2)+1) then convert(decimal(4,1), q.Grade)/2 else 0 end end end) LowerQ , SUM(case when q.GradeCount % 2 = 1 then case when ((q.GradeCount - 1)/2) % 2 = 1 then case when q.rn = q.GradeCount - ((((q.GradeCount-1)/2)+1)/2) + 1 then q.Grade else 0 end else case when q.rn in (q.GradeCount - (((q.GradeCount-1)/2)/2) + 1,q.GradeCount - ((((q.GradeCount-1)/2)/2)+1) + 1) then convert(decimal(4,1), q.Grade)/2 else 0 end end else case when (q.GradeCount/2) % 2 = 1 then case when q.rn = q.GradeCount - (((q.GradeCount/2)+1)/2) + 1 then q.Grade else 0 end else case when q.rn in (q.GradeCount - ((q.GradeCount/2)/2) + 1,q.GradeCount - (((q.GradeCount/2)/2)+1) + 1) then convert(decimal(4,1), q.Grade)/2 else 0 end end end) UpperQfrom qOrder qgroup by q.CourseYear[/code]Wed, 23 Jan 2013 15:51:54 GMTroryp 96873http://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxHiThought I would have a go at this. I've used a CTE to order and number the results by course year.I've also created the statistics in separate queries to try and make it a be easier to manage and read.To determine the medians I have used rounding to determine low and high row number for each median. These can be the same number. There is probably a nicer way to do this:-)I've left the columns in the queries that I was using to validate my median choices.They are all joined together in the final query. I have also added a Geometry to visualize it.[code="sql"];with cte as ( select ROW_NUMBER() OVER (PARTITION BY year(CourseDate) ORDER BY GRADE) RN, COUNT(*) OVER (PARTITION BY year(CourseDate)) C, ROUND(((COUNT(*) OVER (PARTITION BY year(CourseDate)) + 1) / 2.0) - .1, 0) HC1, ROUND(((COUNT(*) OVER (PARTITION BY year(CourseDate)) + 1) / 2.0) + .1, 0) HC2, year(CourseDate) CourseYear, CAST(Grade AS NUMERIC(3)) Grade from EStats ) ,minmax as ( select courseYear, min(Grade) minGrade, max(Grade) maxGrade, min(c) c from cte group by courseYear ) ,median as ( select courseYear, avg(grade) medianGrade , cast(min(hc1) as int) hc1, cast(min(hc2) as int) hc2 from cte where rn in (hc1, hc2) group by courseYear ) ,lowQtr as ( select courseyear, avg(grade) lowQtrGrade ,cast(min(round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 - .1,0)) as int) l1,cast(min(round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 + .1,0)) as int) l2 from cte where rn in (round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 - .1,0),round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 + .1,0)) group by courseyear ) ,highQtr as ( select courseyear, avg(grade) highQtrGrade ,cast(min(round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 - .1,0) + hc1) as int) h1,cast(min(round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 + .1,0) + hc1) as int) h2 from cte where rn in (round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 - .1,0) + hc1,round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 + .1,0) + hc1) group by courseyear )select mm.courseYear, cast(md.medianGrade as numeric(5,2)) Median, cast(mm.maxGrade as numeric(5,2)) Max, cast(mm.minGrade as numeric(5,2)) Min, cast(lq.lowQtrGrade as numeric(5,2)) LowerQ, cast(hq.highQtrGrade as numeric(5,2)) UpperQ, Geometry::STGeomFromText( 'MULTILINESTRING((' + cast(cast(minGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 - 2.5 as varchar(5)) + ',' + cast(cast(minGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 + 2.5 as varchar(5)) + '),(' + -- minTick cast(cast(maxGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 - 2.5 as varchar(5)) + ',' + cast(cast(maxGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 + 2.5 as varchar(5)) + '),(' + -- maxTick cast(cast(medianGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 - 2.5 as varchar(5)) + ',' + cast(cast(medianGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 + 2.5 as varchar(5)) + '),(' + -- medianTick cast(cast(lowQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 - 2.5 as varchar(5)) + ',' + cast(cast(lowQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 + 2.5 as varchar(5)) + '),(' + -- lowerTick cast(cast(highQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 - 2.5 as varchar(5)) + ',' + cast(cast(highQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 + 2.5 as varchar(5)) + '),(' + -- upperTick cast(cast(minGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 as varchar(5)) + ',' + cast(cast(lowQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 as varchar(5)) + '),(' + -- lowWhisker cast(cast(highQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 as varchar(5)) + ',' + cast(cast(maxGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 as varchar(5)) + '),(' + -- highWhisker cast(cast(lowQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 - 2.5 as varchar(5)) + ',' + cast(cast(highQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 - 2.5 as varchar(5)) + '),(' + -- box1 cast(cast(lowQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 + 2.5 as varchar(5)) + ',' + cast(cast(highQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 + 2.5 as varchar(5)) + -- box2 + '))' ,0) graphfrom minmax mm inner join median md on md.courseyear = mm.courseyear inner join lowQtr lq on lq.courseyear = mm.courseyear inner join highQtr hq on hq.courseyear = mm.courseyear[/code]Wed, 23 Jan 2013 14:50:38 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxOkay, two posts above does have the correct dataset (which was the original dataset). I was confused for a moment whether the median in a quartile included the two averaged numbers when total row count is even. It does. In the case of the values 1 through 10 the median would be 5.5, and the quartiles would be the median of everything above 5.5 (6, 7, [b]8[/b], 9, 10) and below 5.5 (5, 4, [b]3[/b], 2, 1). So the upper quartile would be 8 and the lower quartile would be 3.Sorry for the momentary confusion!Wed, 23 Jan 2013 13:33:10 GMTDataAnalyst011http://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxEDIT: On review, the original dataset above is correct. I really apologize for the confusion. I'll put in back in when I get back to my office.Wed, 23 Jan 2013 11:30:37 GMTDataAnalyst011http://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxThanks a ton, Dwain.Just for background (may not be needed) here are a couple of links regarding the math for box-and-whisker charts (or box plots)[url=http://www.purplemath.com/modules/boxwhisk.htm]http://www.purplemath.com/modules/boxwhisk.htm[/url][url=https://www.khanacademy.org/math/probability/descriptive-statistics/Box-and-whisker%20plots/v/box-and-whisker-plots]https://www.khanacademy.org/math/probability/descriptive-statistics/Box-and-whisker%20plots/v/box-and-whisker-plots[/url]Essentially, I need five things: median, upper quartile, lower quartile, min, and max. Median, min, and max are pretty self explanatory. The lower quartile for a box plot takes the median of all of the numbers below the median for the entire set. The upper quartile takes the median for all the numbers above the median for the entire set. With that in view, here should be the result set for my sample DDL:[code="plain"]Year Median Max Min LowerQ UpperQ2010 90 99 81 88 922011 76 88 69 72.5 84.52012 90.5 97 72 86.5 94.5[/code]Again, thanks alot for taking this on. If I can provide further explanation, please let me know!Wed, 23 Jan 2013 07:45:47 GMTDataAnalyst011http://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspx[quote][b]DataAnalyst011 (1/17/2013)[/b][hr]The output should be something like below with values filled in for x:[code="plain"]Year Median Max Min LowerQ UpperQ2010 x x x x x2011 x x x x x2012 x x x x x[/code][code="sql"]CREATE TABLE EStats ( PersonID VARCHAR(30) NOT NULL, Grade VARCHAR(25) NOT NULL, CourseDate Date NOT NULL);INSERT INTO EStats( PersonID, Grade, CourseDate)VALUES ('100', '91', '2010-03-01'), ('101', '96', '2010-03-01'), ('102', '88', '2010-03-01'), ('103', '92', '2010-03-01'), ('104', '81', '2010-03-01'), ('105', '85', '2010-03-01'), ('106', '91', '2010-03-01'), ('107', '89', '2010-03-01'), ('108', '99', '2010-03-01'), ('109', '88', '2010-03-01'), ('110', '81', '2011-03-02'), ('111', '77', '2011-03-02'), ('112', '88', '2011-03-02'), ('113', '76', '2011-03-02'), ('114', '69', '2011-03-02'), ('115', '70', '2011-03-02'), ('116', '75', '2011-03-02'), ('117', '88', '2011-03-02'), ('118', '76', '2011-03-02'), ('119', '95', '2012-03-01'), ('120', '96', '2012-03-01'), ('121', '90', '2012-03-01'), ('122', '80', '2012-03-01'), ('123', '85', '2012-03-01'), ('124', '94', '2012-03-01'), ('125', '89', '2012-03-01'), ('126', '97', '2012-03-01'), ('127', '94', '2012-03-01'), ('128', '72', '2012-03-01'), ('129', '88', '2012-03-01'), ('130', '91', '2012-03-01')[/code][/quote]I would love to try and help but help me out please. Can you fill in the expected results with exactly the real results that you expect to see from the given sample data?I initially did some fumbling around but I couldn't quite 'ken exactly what you were after so I kind of punted with the suggestion I made earlier.If I have a clear target, I'm usually pretty good at hitting it.Tue, 22 Jan 2013 18:00:34 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxI'm just going to bump this because I rewrote the above post and added what I've got to date...Tue, 22 Jan 2013 13:37:31 GMTDataAnalyst011http://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxOkay, using Dwain's suggestion I'm close to getting everything working. My only problem is the row_number in my inner query isn't working well when the numbers are the same. For instance, when you run my inner query...[code="sql"] SELECT CourseDate, Grade, ROW_NUMBER() OVER ( PARTITION BY LEFT(CourseDate, 4) ORDER BY Grade ASC) AS RowAsc, ROW_NUMBER() OVER ( PARTITION BY LEFT(CourseDate, 4) ORDER BY Grade DESC) AS RowDesc FROM EStats[/code]...you'll notice it does this for 2010-03-01:RowAsc10986753421I think that is throwing off the snippet I added which Dwain suggested (THANKS, by the way!). Any suggestions on how to fix this?For those interested, here's what I have. I'm sure its about the worst way I could do this, but its all I've got at this stage in my SQL knowledge! Once I get the row_number ordering issue worked out, I think it will work.[code="sql"]WITH Q3 AS( SELECT CourseDate, AVG(CAST(Grade AS Numeric)) AS Median FROM ( SELECT CourseDate, Grade, ROW_NUMBER() OVER ( PARTITION BY LEFT(CourseDate, 4) ORDER BY Grade ASC) AS RowAsc, ROW_NUMBER() OVER ( PARTITION BY LEFT(CourseDate, 4) ORDER BY Grade DESC) AS RowDesc FROM EStats )x WHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1) GROUP BY CourseDate --ORDER BY CourseDate),Q2 AS( SELECT x.CourseDate, AVG(CAST(Grade AS Numeric)) AS LowerQuartile FROM ( SELECT Estats.CourseDate, Estats.Grade, ROW_NUMBER() OVER ( PARTITION BY LEFT(EStats.CourseDate, 4) ORDER BY Grade ASC) AS RowAsc, ROW_NUMBER() OVER ( PARTITION BY LEFT(Estats.CourseDate, 4) ORDER BY Grade DESC) AS RowDesc FROM EStats JOIN Q3 on EStats.CourseDate = Q3.CourseDate WHERE EStats.Grade < Q3.Median )x WHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1) GROUP BY x.CourseDate),Q4 AS( SELECT x.CourseDate, AVG(CAST(Grade AS Numeric)) AS UpperQuartile FROM ( SELECT Estats.CourseDate, Estats.Grade, ROW_NUMBER() OVER ( PARTITION BY LEFT(EStats.CourseDate, 4) ORDER BY Grade ASC) AS RowAsc, ROW_NUMBER() OVER ( PARTITION BY LEFT(Estats.CourseDate, 4) ORDER BY Grade DESC) AS RowDesc FROM EStats JOIN Q3 on EStats.CourseDate = Q3.CourseDate WHERE EStats.Grade > Q3.Median )x WHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1) GROUP BY x.CourseDate)SELECT Q3.CourseDate, Q3.Median AS Median, Q2.LowerQuartile, Q4.UpperQuartile, MIN(EStats.Grade) AS Min, MAX(EStats.Grade) AS MaxFROM Q3 JOIN Q2 ON Q3.CourseDate = Q2.CourseDate JOIN Q4 ON Q3.CourseDate = Q4.CourseDate JOIN EStats ON Q3.CourseDate = EStats.CourseDateGROUP BY Q3.CourseDate, Q3.Median, Q2.LowerQuartile, Q4.UpperQuartileORDER BY Q3.CourseDate[/code]Tue, 22 Jan 2013 08:43:29 GMTDataAnalyst011http://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxBelow another attempt, but I seem to be running into the same problem. For instance, 2012-03-01 should produce 90.5, but the below gives me 89.25. [code="sql"]SELECT CourseDate, AVG(CAST(Grade AS Numeric)) FROM( SELECT CourseDate, Grade, ROW_NUMBER() OVER ( PARTITION BY PersonID ORDER BY Grade ASC, CourseDate ASC) AS RowAsc, ROW_NUMBER() OVER ( PARTITION BY PersonID ORDER BY Grade DESC, CourseDate DESC) AS RowDesc FROM EStats)xWHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)GROUP BY CourseDateORDER BY CourseDate[/code][b]UPDATE[/b]: I [i]think[/i] the problem is my PARTITION BY isn't working. In other words, the inner query produces a row_number with all 1's so there is nothing to match. Any ideas on a fix?Tue, 22 Jan 2013 08:33:49 GMTDataAnalyst011http://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxAlright, its time to confess I'm still not getting this. Would anyone mind taking my original DDL and calculating median and lower/upper quartiles? I think if I can see it with my example data it would click for me. Any help is [i]really[/i] appreciated!Tue, 22 Jan 2013 07:38:59 GMTDataAnalyst011http://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxMaybe you need to:[code="sql"]PARTITION BY LEFT(CourseDate, 4)[/code]?Thu, 17 Jan 2013 18:24:57 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxActually, I'm having some trouble converting over the example to my sample table above. I just took the grades for 03-01-2012. The median should be the average between 90 and 91 (so 90.5), correct? This gets that result set:[code="sql"]SELECT grade FROM estatsWHERE CourseDate = '2012-03-01'ORDER BY grade DESC [/code]But when I run the below (which is an attempt to convert the example to my sample data above, I get 89.25. I'm sure I'm making a dumb mistake somewhere, but any help is appreciated.[code="sql"]WITH cte AS( SELECT EStats.PersonID, EStats.Grade, ROW_NUMBER() OVER(PARTITION BY EStats.PersonID ORDER BY EStats.GRADE) AS RN, COUNT(*) OVER(PARTITION BY EStats.PersonID) AS Cnt FROM EStats WHERE EStats.CourseDate = '2012-03-01')SELECT AVG(CAST(cte.Grade AS Numeric)) AS MedianFROM cteWHERE RN IN((cte.Cnt + 1) / 2, (Cnt + 2) / 2)[/code]Thu, 17 Jan 2013 16:40:33 GMTDataAnalyst011http://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxAwesome. Thanks a ton!Thu, 17 Jan 2013 14:27:52 GMTDataAnalyst011http://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxI had to do this as well, so you get my supply of bookmarks to help you on your way:[url=http://www.sqlmag.com/article/tsql3/calculating-the-median-gets-simpler-in-sql-server-2005]http://www.sqlmag.com/article/tsql3/calculating-the-median-gets-simpler-in-sql-server-2005[/url][url=http://sqlblog.com/blogs/adam_machanic/archive/2006/12/18/medians-row-numbers-and-performance.aspx]http://sqlblog.com/blogs/adam_machanic/archive/2006/12/18/medians-row-numbers-and-performance.aspx[/url][url=http://sqlblog.com/blogs/peter_debetta/archive/2006/12/20/Medians_Actual_Query_Cost_and_Statistics.aspx]http://sqlblog.com/blogs/peter_debetta/archive/2006/12/20/Medians_Actual_Query_Cost_and_Statistics.aspx[/url]The first one by Itzik Ben-Gan is the one I used.And lastly, a link to one of my prior posts here on SSC. It includes some add'l explanatory notes I wrote to remind myself how Ben-Gan's logic actually works:[url=http://www.sqlservercentral.com/Forums/Topic923660-338-1.aspx#bm1105837]http://www.sqlservercentral.com/Forums/Topic923660-338-1.aspx#bm1105837[/url]HTH,RichThu, 17 Jan 2013 13:35:09 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxAssume it is the median value that has you stumped, try reading this, it works for myself.[url]http://www.mssqltips.com/sqlservertip/2523/script-to-calculate-the-median-value-for-sql-server-data/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20111030[/url]Thu, 17 Jan 2013 13:22:22 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1408572-391-1.aspxHi All - My boss asked me to create a boxchart using SSRS for some of our data. I read articles all morning but honestly, I'm at a loss on how accomplish this. I think I'm growing in my SQL knowledge, but this one has me stumped. I created a sample table that looks like what I'll be working with below. If anyone could lend a hand it would really help me!The output should be something like below with values filled in for x:[code="plain"]Year Median Max Min LowerQ UpperQ2010 x x x x x2011 x x x x x2012 x x x x x[/code][code="sql"]CREATE TABLE EStats ( PersonID VARCHAR(30) NOT NULL, Grade VARCHAR(25) NOT NULL, CourseDate Date NOT NULL);INSERT INTO EStats( PersonID, Grade, CourseDate)VALUES ('100', '91', '2010-03-01'), ('101', '96', '2010-03-01'), ('102', '88', '2010-03-01'), ('103', '92', '2010-03-01'), ('104', '81', '2010-03-01'), ('105', '85', '2010-03-01'), ('106', '91', '2010-03-01'), ('107', '89', '2010-03-01'), ('108', '99', '2010-03-01'), ('109', '88', '2010-03-01'), ('110', '81', '2011-03-02'), ('111', '77', '2011-03-02'), ('112', '88', '2011-03-02'), ('113', '76', '2011-03-02'), ('114', '69', '2011-03-02'), ('115', '70', '2011-03-02'), ('116', '75', '2011-03-02'), ('117', '88', '2011-03-02'), ('118', '76', '2011-03-02'), ('119', '95', '2012-03-01'), ('120', '96', '2012-03-01'), ('121', '90', '2012-03-01'), ('122', '80', '2012-03-01'), ('123', '85', '2012-03-01'), ('124', '94', '2012-03-01'), ('125', '89', '2012-03-01'), ('126', '97', '2012-03-01'), ('127', '94', '2012-03-01'), ('128', '72', '2012-03-01'), ('129', '88', '2012-03-01'), ('130', '91', '2012-03-01')[/code]Thu, 17 Jan 2013 12:45:46 GMTDataAnalyst011