#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep 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 09:46 AM. Reason: Question Andwered, marked title as 'Solved'
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0

    Thumbs up Thank You !!


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

    Thanks again for your help!

IMN logo majestic logo threadwatch logo seochat tools logo