Top #-% of Total-By Year/Qtr - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Top #-% of Total-By Year/Qtr

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/qtr
I can get the following....

Reason ReasonCt %
Not Disabled 1483 48
Over property limit 241 8
Has Ins Covg 238 8

I 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
Tbl
GROUP 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/qtr

2011 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
	Tbl
GROUP 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],Reason
ORDER BY
	[% of Total (rsnct/qtrTotal)] DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources