I am joining two table with left join to get sum results but its giving wrong results. here is my
table1
| id | event | currency | amount |
|----|-------|----------|--------|
| 1 | HSA | USD | 2000 |
| 2 | DMME | USD | 3000 |
| 3 | HSI | INR | 1500 |
| 4 | HSI | INR | 1500 |
table2
| id | table1_id | rcvamount | adjamount |
|----|-----------|-----------|-----------|
| 1 | 1 | 1980 | 20 |
| 2 | 2 | 1000 | 180 |
| 3 | 2 | 1500 | 20 |
| 4 | 4 | 1487 | 13 |
here is my query
SELECT
T1.event,T1.currency,
SUM(T1.total) AS Totalvalue,
SUM(T1.received) AS Received,
(T1.bal) AS balance
FROM (SELECT
table1.id,
table1.amount AS total,
SUM(table2.rcvamount+table2.adjamount) AS received,
((table1.amount)- sum(table2.rcvamount+table2.adjamount)) AS bal,
table1.event,
table1.currency
FROM table1 LEFT JOIN table2 ON table1.id=table2.table1_id
group by table1.id)T1
Group By T1.event,T1.currency
But when i run this query giving wrong results as below.when it comes to third event called HSI does not have any row for id number 3 in table2. Results should be group by event and currency.
| event | currency | Totalvalue | Received | balance |
|-------|----------|------------|----------|---------|
| DMME | USD | 3000 | 2700 | 300 |
| HSA | USD | 2000 | 2000 | 0 |
| HSI | INR | 3000 | 1500 | NULL |
But actual results should be as follows
| event | currency | Totalvalue | Received | balance |
|-------|----------|------------|----------|---------|
| DMME | USD | 3000 | 2700 | 300 |
| HSA | USD | 2000 | 2000 | 0 |
| HSI | INR | 3000 | 1500 | 1500 |
I am not getting where my query goes wrong .Please help me to sort this.Thank you.