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 |
pungigi
Starting Member
5 Posts |
Posted - 2013-02-15 : 12:42:45
|
I am trying to get to the top 3 reasons overall, I want the % of total for each reason, I then want to group by year/qtrI can get the following....Reason ReasonCt %Not Disabled 1483 48Over property limit 241 8Has Ins Covg 238 8I am having problems then getting it to break on year/qtr.I would expect to see...Year Qtr Reason RsnCt % of Total (rsnct/qtrTotal)2011 Q1 Rsn1 181 48%2011 Q1 Rsn2 36 9%2011 Q1 Rsn3 20 5% 2011 Q2 Rsn1 195 46%2011 Q2 Rsn2 50 12%2011 Q2 Rsn3 26 6% 2011 Q3 Rsn1 203 47%2011 Q3 Rsn2 52 12%2011 Q3 Rsn3 27 6% 2011 Q4 Rsn1 135 43%2011 Q4 Rsn2 28 9%2011 Q4 Rsn3 22 7% 2012 Q1 Rsn1 177 48%2012 Q1 Rsn2 36 10%2012 Q1 Rsn3 22 6% 2012 Q2 Rsn1 186 47%2012 Q2 Rsn2 43 11%2012 Q2 Rsn3 18 5% 2012 Q3 Rsn1 227 56%2012 Q3 Rsn2 31 8%2012 Q3 Rsn3 20 5% 2012 Q4 Rsn1 179 51%2012 Q4 Rsn2 30 9%2012 Q4 Rsn3 18 5% Any help would be GREATLY appreciated. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-15 : 13:12:41
|
[code]SELECT [Year],[Qtr],Reason, COUNT(*) AS RsnCt, 100.0*COUNT(*)/SUM(COUNT(*)) OVER() AS ]% of Total (rsnct/qtrTotal)]FROM TblGROUP BY [Year],[Qtr],Reason[/code] |
|
|
pungigi
Starting Member
5 Posts |
Posted - 2013-02-15 : 13:29:17
|
ALMOST & THANK YOU, that works but is giving me the % of All, I need the % by year/qtr2011 Q1 rsn 1 181 5.8% ****************this is what yours returns 181 (rsn-y-q)/2818 (total)2011 Q1 rsn 1 181 48% ****************this is what I need 181 (rsn-y-q)/380 (total of year-qtr) |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-15 : 13:45:21
|
I didn't quite follow the logic, but you should be able to get what you need by adding a PARTITION BY clause (see in red).SELECT [Year],[Qtr],Reason, COUNT(*) AS RsnCt, 100.0*COUNT(*)/SUM(COUNT(*)) OVER(PARTITION BY [Year],[Qtr]) AS [% of Total (rsnct/qtrTotal)]FROM TblGROUP BY [Year],[Qtr],Reason If that does not give you what you are looking for change the partition columns to Year,Reason or some other combination. |
|
|
pungigi
Starting Member
5 Posts |
Posted - 2013-02-15 : 15:43:09
|
PERFECT.... THANK YOU!!! |
|
|
pungigi
Starting Member
5 Posts |
Posted - 2013-02-15 : 15:53:10
|
ok, now they want one additional step, they want the TOP 3 reason OVERALL broken down like above. Any ideas, tried the logical top 3 and it returns top 3 alphabetical... |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-15 : 16:06:38
|
If you order by the percent total desc, that should give the top 3. Is that what you tried?SELECT TOP (3) ....GROUP BY [Year],[Qtr],ReasonORDER BY [% of Total (rsnct/qtrTotal)] DESC |
|
|
|
|
|
|
|