How to sum two column from different Table in H2 Database

Question

I have two tables in database called purchase AND sales Tables as follow

Table purchases

----------------------------
ID  PRODUCT   QTY 
----------------------------
1   APPLE     100
2   BANANA    200
3   GRAPES    150
4   AVACADO   110

Table Sales

----------------------------
ID  PRODUCT   QTY 
----------------------------
1   APPLE     50
2   BANANA    200
3   GRAPES    100

Here in the above tables i have purchase and sales i want to do subtraction from purchases quantity with sales quantity

and get the output like this

Output
----------------------------
ID  PRODUCT   QTY 
----------------------------
1   APPLE     50
2   BANANA    0
3   GRAPES    50
4   AVACADO   110

i written query but not working

select id, product, (purchases.qty)-(sales.qty) from purchases left join sales on purchases.id=sales.id

Not working, can you please explain or post the error you are getting — M Khalid Junaid, Sep 08 '17 at 11:56
Possible duplicate of [PHP & MYSQL: How to resolve ambiguous column names in JOIN operation?](https://stackoverflow.com/questions/431391/php-mysql-how-to-resolve-ambiguous-column-names-in-join-operation) — Shadow, Sep 08 '17 at 12:05
You need to provide the table name for all columns because they are the same in the two tables. — Shadow, Sep 08 '17 at 12:06
Hey thank you both @Shadow and @M Khalid Junaid you were right problem solved — Intact Abode, Sep 08 '17 at 12:10

score 1 · Accepted Answer · answered Sep 08 '17 at 12:09

1

Solution

SELECT purchases.id,purchases.product, (purchases.qty - IF (sales.qty IS NULL, 0, sales.qty)) as qty FROM pruchases LEFT JOIN sales ON purchases.product = sales.product

answered Sep 08 '17 at 12:09

Max Gaurav

1,835
2
13
26

hey @Max Gaurav, to be frankly i found the solution before your answered it, and next problem raised with null, and your answer fixed it cheers for that – Intact Abode Sep 08 '17 at 12:14

score 0 · Answer 2 · answered Sep 08 '17 at 12:18

0

See all three Images your problem may be solved.

SALES TABLE [Same as Sales Table]1

PURCHASE TABLE [Same as Purchase Table]2

RESULTANT IMAGE [Resultant Image With Query]3

answered Sep 08 '17 at 12:18

Sanjay Kumaar

690
7
17

How to sum two column from different Table in H2 Database

2 Answers2