2. PHPRunner General questions
  3. Sum of two table rows
This topic is locked

Sum of two table rows

 		9/18/2009 12:25:05 PM
PHPRunner General questions
coolbyte author

Hello,
I would like to use the sum of 2 Tables in one list, e.g.
select user, sum(numbers) from table1 group by user;

select user, sum(numbers) from table2 group by user;
The output shall be
user, sum(numbers of table1), sum(numbers of table2)
How can I handle that?
Regards
Thomas
J
 		jsuisman 9/18/2009

I would say you should use a subquery. This is for MySql:



SELECT user, sum(numbers) as sum1, (SELECT sum(numbers) FROM table2 t2 WHERE t1.user = t2.user GROUP BY user) as sum2 FROM table1 t1 GROUP BY user


This assumes that you're using user as a primary key or at least a unique field, because the subquery cannot return more than one row. (which is the error you will get back if it does)
The way I joined the two tables, a user would only show up if they were in both tables. You could use a left or right join if you wanted it to perform differently.
Good luck,
Jarred
coolbyte author 9/18/2009

I tried



SELECT int_mastunden.name, sum(abgerechnet) as sum1, (SELECT sum(minuten) FROM int_ma_daytime where int_ma_daytime.user=int_mastunden.name GROUP BY user) as sum2 FROM int_mastunden GROUP BY int_mastunden.name


but got the following error message:

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 sum(minuten) FROM int_ma_daytime where int_ma_daytime.us
J
 		jsuisman 9/18/2009

Ok, sorry about that, didn't test my code. I guess there was a problem <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=44170&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
Try this:



SELECT int_mastunden.name, sum(abgerechnet) as sum1, sum(minuten) as sum2 FROM int_mastunden, int_ma_daytime WHERE int_mastunden.name = int_ma_daytime.user GROUP BY int_mastunden.name


Once again this join will only work if the user is in both tables. Could take a while to run depending on size of tables as well.
Good luck,
Jarred