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.
Your are using LEFT JOIN so you will have all first table rows even they havent any match in the second table by the required join.
In your case in the first table the id=3 have no match in the second table so you get the NULL results. (SELECT * FROM table2 WHERE table1_id =3 will give 0 results).
To avoid this use INNER JOIN :
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
INNER JOIN table2 ON table1.id = table2.table1_id
GROUP BY table1.id
)T1
GROUP BY T1.event, T1.currency
try this ,COALESCE() will transform each null to 0
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( COALESCE( table2.rcvamount, 0 ) + COALESCE( table2.adjamount, 0 ) ) AS received, (
COALESCE( table1.amount, 0 ) - sum( COALESCE( table2.rcvamount, 0 ) + COALESCE( table2.adjamount, 0 ) )
) 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
it will work for you
select tp.id,tp.event,tp.currency,
tp.totalvalue,
tr.received,
(tp.totalvalue - tr.received) AS balance
from
(
select max(id)id,event,currency,amount,sum(amount)as totalvalue from t1
group by t1.event
)tp
inner join
(
select id,table1_id,rcvamount,adjamount,(sum(rcvamount) + sum(adjamount))as received
from t2
group by table1_id
order by t2.adjamount desc
)tr
on tp.id = tr.id
group by tp.event