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 |
sqlmjkm
Starting Member
14 Posts |
Posted - 2013-02-07 : 07:52:49
|
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 AmountCARS ----- 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 ----- AmountCARS ----- A ----- 1CARS ----- B ----- 2CARS ----- C ----- 3TRUCKS ----- A ----- 10TRUCKS ----- B ----- 20The nested query would be the total of each car and type divided by the total cars/trucks. Group ----- Total AmountCARS ----- 6TRUCKS ----- 30Thank you in advance!! |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-07 : 07:58:56
|
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; |
|
|
sqlmjkm
Starting Member
14 Posts |
Posted - 2013-02-07 : 08:27:27
|
Would I combine the 2?? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-07 : 08:46:47
|
No, use one or the other. second query for your first result set and first query for the second |
|
|
sqlmjkm
Starting Member
14 Posts |
Posted - 2013-02-07 : 09:18:49
|
I think this is working! Thank you! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-07 : 09:41:51
|
You are very welcome - glad to help. |
|
|
sqlmjkm
Starting Member
14 Posts |
Posted - 2013-02-07 : 09:42:19
|
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? |
|
|
|
|
|