SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Top #-% of Total-By Year/Qtr		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pungigi
Starting Member

5 Posts
Posted - 02/15/2013 :  12:42:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1530 Posts
Posted - 02/15/2013 :  13:12:41  Show Profile  Reply with Quote 
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
Go to Top of Page

pungigi
Starting Member

5 Posts
Posted - 02/15/2013 :  13:29:17  Show Profile  Reply with Quote
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)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1530 Posts
Posted - 02/15/2013 :  13:45:21  Show Profile  Reply with Quote
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.
Edited by - James K on 02/15/2013 13:46:57
Go to Top of Page

pungigi
Starting Member

5 Posts
Posted - 02/15/2013 :  15:43:09  Show Profile  Reply with Quote
PERFECT.... THANK YOU!!!
Go to Top of Page

pungigi
Starting Member

5 Posts
Posted - 02/15/2013 :  15:53:10  Show Profile  Reply with Quote
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...
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1530 Posts
Posted - 02/15/2013 :  16:06:38  Show Profile  Reply with Quote
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
Edited by - James K on 02/15/2013 16:09:55
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000