join with sum case and group by in two tables

31 Views Asked by At

enter image description here

enter image description here

Result ----

enter image description here

but right total_used_reward is -275

My sql query :

SELECT ure.*,
       COUNT(DISTINCT(ure.id)) as total_referrer, 
       SUM(CASE WHEN uar.txn_type=1 THEN uar.reward ELSE 0 END) as total_used_reward 
FROM user_refer_earn ure 
JOIN user_activity_rewards uar ON uar.user_id = ure.code_owner 
WHERE ure.code_owner = 1 
GROUP BY ure.code_owner

right total_used_reward is -275

0

There are 0 best solutions below