Forums: » Register « |  Free Tools |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

New Free Tools on Dev Shed!

#1
November 8th, 2012, 02:32 PM
 estafford
Registered User

Join Date: Nov 2012
Posts: 2
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.

and finally
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:

[MYSQL]
SELECT name,
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;
[/MYSQL]

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 10:46 AM. Reason: Question Andwered, marked title as 'Solved'

#2
November 9th, 2012, 07:06 AM
 r937
SQL Consultant

Join Date: Feb 2003
Posts: 26,824
Time spent in forums: 3 Months 1 Week 4 Days 9 h 19 m
Reputation Power: 4208
Code:
```SELECT name
, SUM(column_1) as 'Attempt'
, SUM(column_2) as 'Success'
, SUM(column_2) * 100.0 / SUM(column_1) as 'Percent_of_own'
, SUM(column_1) * 100.0 /
( SELECT SUM(column_1)
FROM activelog
WHERE column_1 > 0
AND entrydate BETWEEN '2012/01/01'
AND '2013/01/01' ) AS 'Percent_of_all'
FROM activelog
WHERE column_1 > 0
AND entrydate BETWEEN '2012/01/01'
AND '2013/01/01'
GROUP
BY name```
__________________
r937.com | rudy.ca

#3
November 9th, 2012, 10:43 AM
 estafford
Registered User

Join Date: Nov 2012
Posts: 2
Time spent in forums: 47 m 29 sec
Reputation Power: 0
Thank You !!

Quote:
 Originally Posted by r937 Code: ```SELECT name , SUM(column_1) as 'Attempt' , SUM(column_2) as 'Success' , SUM(column_2) * 100.0 / SUM(column_1) as 'Percent_of_own' , SUM(column_1) * 100.0 / ( SELECT SUM(column_1) FROM activelog WHERE column_1 > 0 AND entrydate BETWEEN '2012/01/01' AND '2013/01/01' ) AS 'Percent_of_all' FROM activelog WHERE column_1 > 0 AND entrydate BETWEEN '2012/01/01' AND '2013/01/01' GROUP BY name```

@r937
Brilliant! Thank you. Your example is super clean and easy to follow. I came close in one of my attempts, but had one of the parens in the wrong place - Select(SUM... instead of (SELECT SUM ...

 Viewing: Dev Shed Forums > Databases > MySQL Help > How to compare a "grand total" sum() to a group by sum?