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
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;
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?
Nested SQL Query - % of Group Total
Posted - 02/07/2013 : 07:52:49
