mysql select sum 2 rows from different tables

Question

I have been trying to find the correct way to sum 2 rows from 2 different tables.

I can easily identify the rows I want to sum using these two queries:

select * 
from   vui_trading_review_form_client 
where  month = '201202' and client_id='TOTALS';

+--------+------------+-----------+-----------------+-----------------+-----------------+----------------+----------+
| month  | dt_end     | client_id | amt_balance_GBP | amt_balance_EUR | amt_balance_USD | dt_working_day | order_no |
+--------+------------+-----------+-----------------+-----------------+-----------------+----------------+----------+
| 201202 | 2012-02-29 | TOTALS    |            2    |             3   |               4 | 2012-02-29     |        2 |
+--------+------------+-----------+-----------------+-----------------+-----------------+----------------+----------+


select * 
from   vui_trading_review_form_bank 
where  month = '201202' and provider='TOTALS';

+--------+------------+----------+-----------------+-----------------+-----------------+----------+
| month  | dt_end     | provider | amt_balance_GBP | amt_balance_EUR | amt_balance_USD | order_no |
+--------+------------+----------+-----------------+-----------------+-----------------+----------+
| 201202 | 2012-02-29 | TOTALS   |              1  |               1  |              1 |        3 |
+--------+------------+----------+-----------------+-----------------+-----------------+----------+

What I would like to achieve is a table which looks as follows

+-----------------+-----------------+-----------------+
| amt_balance_GBP | amt_balance_EUR | amt_balance_USD |
+-----------------+-----------------+-----------------+
| 1               |          2      |             3   |
+-----------------+-----------------+-----------------+

the first 3 totals minus the second three totals.

I have tried a join but i'm really struggling to get the right result.

Any suggestions would be greatly appreciated.

Your two queries are from the same table. I assume that's a mistake? Also, are you adding or subtracting those values (looks like subtracting)? — John Flatness
– John Flatness, Commented May 24, 2012 at 23:23
the name of the two tables are the same? vui_trading_review_form_client? — John Woo
– John Woo, Commented May 24, 2012 at 23:45
sorry this is a mistake the second table is vui_trading_review_form_bank — Sam Corbet
– Sam Corbet, Commented May 24, 2012 at 23:48
ah ok, check my answer below. you need to use INNER JOIN or LEFT JOIN — John Woo
– John Woo, Commented May 24, 2012 at 23:50

John Woo · Accepted Answer · 2012-05-24 23:55:35Z

1

Try this:

SELECT  (a.amt_balance_GBP - b.amt_balance_GBP) Total_GBP,
        (a.amt_balance_EUR - b.amt_balance_EUR) Total_EUR,
        (a.amt_balance_USD - b.amt_balance_USD) Total_USD
FROM vui_trading_review_form_client a INNER JOIN vui_trading_review_form_bank b
        on (a.Client_ID = b. provider) AND
           (a.Month = b.Month)
WHERE a.month = '201202' and b.provider='TOTALS';

or if it does not fit your needs, try to use LEFT JOIN

SELECT  (a.amt_balance_GBP - COALESCE(b.amt_balance_GBP,0)) Total_GBP,
        (a.amt_balance_EUR - COALESCE(b.amt_balance_EUR,0)) Total_EUR,
        (a.amt_balance_USD - COALESCE(b.amt_balance_USD,0)) Total_USD
FROM vui_trading_review_form_client a LEFT JOIN vui_trading_review_form_bank b
        on (a.Client_ID = b. provider) AND
           (a.Month = b.Month)
WHERE a.month = '201202' and b.provider='TOTALS';

edited May 24, 2012 at 23:55

answered May 24, 2012 at 23:49

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sam Corbet Over a year ago

Thank you, this almost works. It ignores the where clause though where month = '201202' and provider='TOTALS'; when I try and add it I get an ambiguous result

John Woo Over a year ago

yes because you need to specify on where the table of the column should be assigned. try to add the alias of the table so you won;t get ambiguous column error.

Sam Corbet Over a year ago

Okay thank you, when I run the query I get 3 warnings and the table produced gives all three values as 0. what does the 0 mean next to b.amt_balance_GBP?

John Woo Over a year ago

do you mean COALESCE()? it a function that converts null into valid value eg. 0.

BenSchro10 · Accepted Answer · 2012-05-25 00:00:58Z

1

Are you just trying to do this:

select (a.amt_balance_GBP  - b.amt_balance_GBP) as GBP, 
   (a.amt_balance_EUR - b.amt_balance_EUR) as EUR,
   (a.amt_balance_USD - b.amt_balance_USD) as USD
from  vui_trading_review_form_client a, vui_trading_review_form_bank b
where a.month = '201202' and a.client_id ='TOTALS' and
      a.month = b.month and a.client_id = b.provider

edited May 25, 2012 at 0:00

answered May 24, 2012 at 23:50

BenSchro10

3261 silver badge7 bronze badges

3 Comments

Sam Corbet Over a year ago

Thank you for this response, yes this looks what I am trying to do. However when I run this I get a syntax error?

BenSchro10 Over a year ago

I had a typo in there, this should work in Sybase ASE or SQL Server im not sure about others though.

Sam Corbet Over a year ago

Thank you, again I get the same problem as with @johntotetwoo suggestion - all the sums equal 0. perhaps it is because I am using mysql

Collectives™ on Stack Overflow

mysql select sum 2 rows from different tables

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related