SUM() across multiple tables

Databases
1

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.


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

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:

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

3

I see. When running that query it returns a result set like this:



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

okay, now try this –

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
5

I get an error

#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

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

:slight_smile:

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

7

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

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

sorry, no, i’ve replied in far too many threads to be able to remember who posted what and which version they’re on

sure you can

query 1 –

select owner_id
     , sum(revenue) as total_revenue
  from table_2
group by owner_id

query 2 –

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 :slight_smile:

9

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

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

11 
$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

DOH! i forgot about UNION!

thanks, carl

:slight_smile:

13

i thought you were just being obtuse in an attempt to get the OP to upgrade to a newer version of mysql :smiley:

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

Thanks for your help.

I get an output like this:

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:

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

not easily, but it could be done in the PHP code.

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

I managed to fix the numeric keys by simply using sort() after the array has been generated.


$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

by usnig sort, you have just thrown away the link between your data and the userid. try this instead:

$result = mysql_query($sql) or die(mysql_error());
$data = array();
while ($row = mysql_fetch_array($result)) {
  $data[] = $row;
}
sort($data);
print_r($data);
18

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

Thanks again.

19

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?