Nested SQL Query - % of Group Total

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

General SQL Server Forums

New to SQL Server Programming

Nested SQL Query - % of Group Total

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 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
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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources