November 8th, 2012, 01:32 PM
Join Date: Nov 2012
Time spent in forums: 47 m 29 sec
Reputation Power: 0
[SOLVED] How to compare a "grand total" sum() to a group by sum?
I've got a testing table containing names and misc numbers. What i'm trying to do is;
1. get the sum of column_1 for each name within a date range
2. get the sum of column_2 for each name within a date range
3. get the percentage amount of column_1 against column_2 for each name within a date range.
4. get the total sum of column_1 for ALL names within a date range to calculate a percentage of column_1 for each name.
1-3 have been accomplished using 'Group By'. The problem I have now is, How do I include part 4, getting the grand total sum of all rows and not have it affected by 'Group By'? I've had no luck so far.
The working query for parts 1-3 is:
SUM(column_1) as 'Attempt',
SUM(column_2) as 'Success',
(SUM(column_2) * 100) / SUM(column_1) as 'Percent_of_own'
FROM activelog WHERE column_1 > 0 AND
entrydate BETWEEN '2012/01/01' AND '2013/01/01'
GROUP BY name;
This works fine. But now I want to somehow include something like:
(SUM(column_1) * 100) / << Grand Total>> AS 'Percent_of_all"
A sample or pointer to online resources, examples would be appreciated!
Last edited by estafford : November 9th, 2012 at 09:46 AM.
Reason: Question Andwered, marked title as 'Solved'