Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dutchie75
Starting Member
22 Posts |
 Posted - 2009-02-13 : 22:46:20
|
| Does anyone know how to obtain the median summary of a set of records? Not the average, but the MEDIAN? I would have thought it would be an included summary option along with AVERAGE, but no such luck. Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-14 : 04:25:56
|
| [code]SELECT MAX(CASE WHEN Seq =CIELING(Occurance/2.0) THEN YourCol ELSE NULL END)+MAX(CASE WHEN Seq =FLOOR(Occurance/2.0) THEN YourCol ELSE NULL END)/2FROM(SELECT ROW_NUMBER() OVER (ORDER BY YourCol) AS Seq,YourCol,COUNT(YourCol) OVER () AS OccuranceFROM Table)t[/code] |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-14 : 13:52:04
|
| http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/calculating-mean-median-and-mode-with-sq |
|
|
|
Dutchie75
Starting Member
22 Posts |
Posted - 2009-02-24 : 13:16:36
|
| I fianlly have some time to work on this now. visakh16 - do I create a separate dataset for this solution? I'm still quite a novice. I may need a step-by-step instruction if you're able. I'm looking to retrieve the median of a calculated field if that makes any difference.sodeep - I don't have any experience working with temp tables in ssrs so I'm hoping I can make visakh16's solution work. Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-24 : 13:25:06
|
| what all does you need to show in your current report? |
|
|
|
Dutchie75
Starting Member
22 Posts |
Posted - 2009-02-24 : 13:49:46
|
| client#, date1, date2, #days from date1 to date2, manager, and some summary info in the group footers of the table. I want the median of the #days field per group. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-02 : 13:23:16
|
| then what you need is group by client#, date1, date2,manager fields and apply aggregation over date,other fields to get summary info.you may either do grouping in backend query or just bring detail data in dataset and do grouping in report. |
|
|
|
|
|
|
|
|
Median in Reporting Services 2005