Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

3873 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

3873 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

3873 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  
 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.09 seconds. Powered By: Snitz Forums 2000