How do i modify the result of this SQL query?

Question

Here is the curren query

select usr.COUNTRY,
round(ifnull(sum(CASE when de.comment like '%BT%' THEN de.PROFIT END),0),2) AS OCPAY,
round(ifnull(sum(CASE when de.comment like '%XP%' THEN de.PROFIT END),0),2) AS XPAY,
round(ifnull(sum(CASE when de.comment like '%PA%' THEN de.PROFIT END),0),2) AS DRAGONPAY,
round(ifnull(sum(CASE when de.comment NOT like '%UT%' THEN de.PROFIT END),0),2) AS TOTAL_DEPOSIT,
#Markup
Case when
usr.country = 'Philippines' then round(ifnull(sum(de.PROFIT*0.02),0),2) Else
round(ifnull(sum(de.PROFIT*0.0055),0),2)
END AS 'Deposit_Bonus'
FROM svgdbgom_svgmt5db.mt5_users usr
inner join svgdbgom_svgmt5db.mt5_deals de using (login)
WHERE symbol = ''
and profit > 0
and usr.group not like '%test%'
and country <> 'China'
and de.time between '2022-04-01 00:00:00' and '2022-04-12 23:59:59'
and de.comment not like '%UT%'
and de.comment not like '%NT%'
and de.comment not like '%SK%'
and usr.name not like '%FXCE%'
and de.comment like '%2204%'
group by country),



Withdrawal as
(select usr.COUNTRY,
round(ifnull(sum(CASE when de.comment like '%BT%' THEN de.PROFIT END),0),2) AS OCPAY,
round(ifnull(sum(CASE when de.comment like '%XP%' THEN de.PROFIT END),0),2) AS XPAY,
round(ifnull(sum(CASE when de.comment like '%PA%' THEN de.PROFIT END),0),2) AS DRAGONPAY,
round(ifnull(sum(CASE when de.comment NOT like '%UT%' THEN de.PROFIT END),0),2) AS TOTAL_WITHDRAWAL,
# add
Case when
usr.country = 'Philippines' then round(ifnull(sum(de.PROFIT*0.02),0),2) Else
round(ifnull(sum(de.PROFIT*0.012),0),2)
END AS 'Withdrawal_Bonus'
FROM svgdbgom_svgmt5db.mt5_users usr
inner join svgdbgom_svgmt5db.mt5_deals de using (login)
WHERE symbol = ''
and country <> 'China'
and de.time between '2022-04-01 00:00:00' and '2022-04-12 23:59:59'
and usr.group not like '%test%'
and de.comment not like '%NT%'
and de.comment not like '%SK%'
and de.comment like '%Withdrawal%'
group by country
)
select *,case when
country = 'Vietnam' then '31093'
else '31092'
End as 'IB ACC', Deposit_Bonus-withdrawal_Bonus as TOTAL_BONUS from
deposit left join withdrawal
using (country)
order by country desc;

and this the current result Current result

the Total Bonus supposes to equal Deposit Bonus + Withdrawal Bonus. Total Bonus of VietNam is correct but Total Bonus of Phillippines is "Null" instead of 42.00. How can i fix it? thanks, im new to SQL

Answer 1

you need NULL checks in your query.

change the bottom part like this

select *,
case when
country = 'Vietnam' then '31093'
else '31092'
End as 'IB ACC', 
IFNULL(Deposit_Bonus,0.00)-IFNULL(withdrawal_Bonus,0.00) as TOTAL_BONUS from
deposit left join withdrawal
using (country)
order by country desc;