SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SUM() across multiple tables

    Hi guys,
    I am trying to do 2 sums in this mysql query. Firstly I want to get the total amount paid from 'table_1'. I then want to get the total amount earned from 'table_2'. The total amount earned (total_revenue) is returned perfectly everytime.

    However, the total_paid is always multiplied by the number of rows returned from 'table_2'. So lets say the SUM() of rows in table_1 add up to '10' and there are 20 rows returned from table_2 then the value of total_paid becomes
    200.

    I don't know why this is happening...Any ideas how to fix the query?

    Thank you in advance.

    PHP Code:
    select sum(table_1.amount) as total_paid,
            
    sum(table_2.revenue) as total_revenue from table_2
            left join table_1 on table_1
    .userid table_2.owner_id
            group by table_1
    .userid
            order by table_1
    .userid 

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Zaggs
    I don't know why this is happening...
    it's really easy to see why, when you look at how the join actually works before you aggregate with GROUP BY

    run this non-grouping query:
    Code:
    select table_1.userid 
         , table_1.amount
         , table_2.revenue
      from table_2 
    left 
      join table_1 
        on table_1.userid = table_2.owner_id 
    order 
        by table_1.userid 
         , table_1.amount
         , table_2.revenue
    examine the result set and convince yourself that you have joined the tables properly, and that those are indeed the right numbers coming out of the right collumns for the right rows

    now copy/paste the cells into excel and do column totals, and you will see why you are getting the cross join effects -- each table_1.amount is joined to every table_2.revenue, and the sums simply operate on the columns of the join result

    once you've understood this, i'll show you how to fix it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see. When running that query it returns a result set like this:

    PHP Code:
      
    userid  amount  revenue  
    1 0.49 0.00 
    1 0.49 0.00 
    1 0.49 0.00 
    1 0.49 0.00 
    1 0.49 0.00 
    It has the 'amount' on every row returned.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, now try this --
    Code:
    select s2.owner_id
         , s1.total_paid
         , s2.total_revenue
      from (
           select owner_id
                , sum(revenue) as total_revenue
             from table_2
           group by owner_id
           ) as s2
    left outer
      join (
           select userid
                , sum(amount) as total_paid
             from table_1
           group by userid
           ) as s1
        on s1.userid = s2.owner_id
    group
        by s2.owner_id
    order
        by s2.owner_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I get an error

    PHP Code:
    #1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select owner_id  , sum( revenue ) as total_revenue  from table_ 

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i guess you missed reading the PLEASE READ sticky thread at the top of the mysql forum which asks you, if you aren't even on version 4.1 yet, always to mention which version you're on, so that people do not waste time coming up with a solution that you won't even be able to implement



    4.1 has been in production status for about a year and a half, you should really consider upgrading asap

    meanwhile, just run two queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    i guess you missed reading the PLEASE READ sticky thread at the top of the mysql forum which asks you, if you aren't even on version 4.1 yet, always to mention which version you're on, so that people do not waste time coming up with a solution that you won't even be able to implement



    4.1 has been in production status for about a year and a half, you should really consider upgrading asap

    meanwhile, just run two queries

    I have actually read that post now, just thought you may have recognised my username from one of the other threads I created, that you replied to.

    Anyway...so it is not possible to do this query in MySQL version 4? The reason I ask, is this script is going to be given to some of my clients and they may not all be running the latest version of MySQL.

    I don't think I can do it using two queries, because the user id's need to be grouped.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Zaggs
    ... you may have recognised my username from one of the other threads I created, that you replied to.
    sorry, no, i've replied in far too many threads to be able to remember who posted what and which version they're on

    Quote Originally Posted by Zaggs
    I don't think I can do it using two queries, because the user id's need to be grouped.
    sure you can

    query 1 --
    Code:
    select owner_id
         , sum(revenue) as total_revenue
      from table_2
    group by owner_id
    query 2 --
    Code:
    select owner_id
         , sum(amount) as total_paid
      from table_1
    group by owner_id
    now all you have to do is merge the two arrays in your scripting language

    way more code than a simple query, but that's the price you have to pay for being backwards compatible to dinosaur versions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937,
    I seem to be having big problems with this. I can't seem to get the array's merging correctly. See here: http://www.sitepoint.com/forums/showthread.php?t=357249

    Anyway, I was wondering if it is at all possible to do a join on the tables?? Is there a different method to the one you gave above (which just works for newer MySQL versions)

    Thanks in advance...

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't do php, you're on you own there

    as far as "doing a join" is concerned, we've already covered why it won't work unless you can use subqueries to "pre-consolidate" the totals from the two tables separately

    you could generate the results of each of the two queries separately into a temp table (with the additional overhead that creating a temp table entails), and then write a third query to pull back the results from the temp table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $sql "select table_1.userid
                 , 'amount' as type
                 , sum(table_1.amount) as value
              from table1
            group
                by 1
             union all
            select table_2.userid
                 , 'revenue' as type
                 , sum(table_2.revenue) as value
              from table1
            group
                by 1"
    ;
                
    $result mysql_query($sql) or die(mysql_error());

    $data = array();

    while (
    $row mysql_fetch_array()) {
      
    $data[$row['userid']][$row['type']] = $row['value'];


  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    DOH! i forgot about UNION!

    thanks, carl

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i thought you were just being obtuse in an attempt to get the OP to upgrade to a newer version of mysql

    anyway, my little while loop puts the data in to a two-dimensional array where the first dimension is the user id, the second dimension is the type, and the data is the revenue or total value.

  14. #14
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    i thought you were just being obtuse in an attempt to get the OP to upgrade to a newer version of mysql

    anyway, my little while loop puts the data in to a two-dimensional array where the first dimension is the user id, the second dimension is the type, and the data is the revenue or total value.
    Thanks for your help.

    I get an output like this:
    PHP Code:
    Array ( [1] => Array ( [amount] => 1.89 [revenu] => 0.70 ) [5] => Array ( [revenu] => 1.27 ) ) 
    Is there any way to get the numeric key to not show the user id and have the userid as part of the array. Also, if there is no 'amount' in the first table, then can we set a default value to 0?

    An ideal output would be this:
    PHP Code:
    Array ( [0] => Array ( [amount] => 1.89 [revenu] => 0.70 [userid] => 1) [1] => Array ( [amount] => 0.00 [revenu] => 1.27 [userid] => 2) ) 
    Thanks in advance.

  15. #15
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Zaggs
    Is there any way to get the numeric key to not show the user id and have the userid as part of the array.
    not easily, but it could be done in the PHP code.
    Quote Originally Posted by Zaggs
    Also, if there is no 'amount' in the first table, then can we set a default value to 0?
    not with just a small tweak. you will need to loop through the entire array after it's populated from the result set and set the 0 yourself when the data is missing.

  16. #16
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I managed to fix the numeric keys by simply using sort() after the array has been generated.

    PHP Code:
    $result mysql_query($sql) or die(mysql_error()); 
    $data = array(); 
    while (
    $row mysql_fetch_array($result)) { 
    $data[$row['userid']][$row['type']] = $row['value'];
    }
    sort($data);
    print_r($data); 

    I now need to append 'userid' to the existing array '$data'. How do i do this?

  17. #17
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    by usnig sort, you have just thrown away the link between your data and the userid. try this instead:
    PHP Code:
    $result mysql_query($sql) or die(mysql_error());
    $data = array();
    while (
    $row mysql_fetch_array($result)) {
      
    $data[] = $row;
    }
    sort($data);
    print_r($data); 

  18. #18
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    by usnig sort, you have just thrown away the link between your data and the userid. try this instead:
    PHP Code:
    $result mysql_query($sql) or die(mysql_error());
    $data = array();
    while (
    $row mysql_fetch_array($result)) {
      
    $data[] = $row;
    }
    sort($data);
    print_r($data); 

    The above code creates an array for each 'type'. For example, it will create seperate arrays for 'revenu' and 'amount'. I need a way to join these.

    Im sorry for my ignorance but I just can't figure out a way around it.

    Thanks again.

  19. #19
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ah, good point.

    look, there's just no good way to get the results in the format you want without upgrading to mysql 4.1 or newer. what's wrong with the array format i suggested before?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •