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
 General SQL Server Forums
 New to SQL Server Programming
 Nested SQL Query - % of Group Total
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlmjkm
Starting Member

14 Posts

Posted - 02/07/2013 :  07:52:49  Show Profile  Reply with Quote
Hello. I am having trouble getting the correct nested query syntax for this. What I want to do is come up with these results:

Group ----- Type ----- % of Total Amount
CARS ----- A ----- 16.67%
CARS ----- B ----- 33.33%
CARS ----- C ----- 50.00%
TRUCKS ----- A ----- 33.33%
TRUCKS ----- B ----- 66.67%


What my table already contains is:

Group ----- Type ----- Amount
CARS ----- A ----- 1
CARS ----- B ----- 2
CARS ----- C ----- 3
TRUCKS ----- A ----- 10
TRUCKS ----- B ----- 20


The nested query would be the total of each car and type divided by the total cars/trucks.

Group ----- Total Amount
CARS ----- 6
TRUCKS ----- 30


Thank you in advance!!

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 02/07/2013 :  07:58:56  Show Profile  Reply with Quote
One of these?
SELECT [Group],COUNT(*) AS [Total Amount] FROM Tbl GROUP BY [Group]

SELECT [Group],[Type],100.0*Amount/SUM(Amount) OVER( PARTITION BY [Group]) AS [% of Total]
FROM Tbl;
Go to Top of Page

sqlmjkm
Starting Member

14 Posts

Posted - 02/07/2013 :  08:27:27  Show Profile  Reply with Quote
Would I combine the 2??
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 02/07/2013 :  08:46:47  Show Profile  Reply with Quote
No, use one or the other. second query for your first result set and first query for the second
Go to Top of Page

sqlmjkm
Starting Member

14 Posts

Posted - 02/07/2013 :  09:18:49  Show Profile  Reply with Quote
I think this is working! Thank you!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 02/07/2013 :  09:41:51  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page

sqlmjkm
Starting Member

14 Posts

Posted - 02/07/2013 :  09:42:19  Show Profile  Reply with Quote
One more question. With your 2nd query...it is working, however, I need to do a group by first. So I need to Group by [Group],[Type] and SUM the Amount FIRST. (Because there a million detail records) Then based on that total do the 100.0*Amount/SUM(Amount) OVER( PARTITION BY [Group]).... I get an error stating "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." However if I add my Amount in, then I see every million detail record because it hasn't been summarized.. Hopefully makes sense. Is there a way around this?
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