Find Sum of cashbonus on the basis of user id in MySQL-CodePudding

ub_referrerid  ub_redeemerid  ub_referrer_cashbonus    ub_redeemer_cashbonus
   186           190              100                            60
   154           186              100                            60
   190           201              100                            60
   154           189              100                            60
   190           211              100                            60
   154           255              100                            60
   190           297              100                            60

Now I have To Find The Sum Of cash Bonus on the basis of userid suppose I have userid 154 which is present in both ub_referrerid and ub_redeemerid now I have to find the sum of userid 154 from column ub_referrer_cashbonus and ub_redeemer_cashbonus using MySQL

CodePudding user response：

We create a CTE with UNION ALL two queries : one for referers bonuses and one for referees bonuses. We then amalgamate the results.

create table ub(
  ub_referrerid int,
  ub_redeemerid int,
  ub_referrer_cashbonus int,   
  ub_redeemer_cashbonus int);
insert into ub values
 (186 ,190 ,100, 60 ),
(154 ,186, 100 ,60 ),
(190 ,201 ,100, 60 ),
(154 ,189 ,100, 60),
( 190 ,211, 100, 60 ),
(154 ,255 ,100 ,60),
( 190 ,297 ,100, 60);
   
with bonus as (
select 
   ub_referrerid id ,
   sum(ub_referrer_cashbonus) referBonus,
   0 redbonus
from 
   ub
group by
   ub_referrerid
union all 
select 
   ub_redeemerid id,
   0 refCash,
   sum(ub_redeemer_cashbonus) redCash
from 
   ub
group by
   ub_redeemerid
)
select
   b.id ,
   sum(b.referbonus) refBonus,
   sum(b.redBonus) redbonus,
   sum(b.referBonus) sum(redBonus) total
from bonus b
group by id 
order by id ;

 id | refBonus | redbonus | total
--: | -------: | -------: | ----:
154 |      300 |        0 |   300
186 |      100 |       60 |   160
189 |        0 |       60 |    60
190 |      300 |       60 |   360
201 |        0 |       60 |    60
211 |        0 |       60 |    60
255 |        0 |       60 |    60
297 |        0 |       60 |    60

db<>fiddle here

CodePudding user response：

You have to use GROUP BY clause based on ub_referrerid and ub_redeemerid with same alias userid. Then use UNION ALL (In this operation, column names with alias must be same).

SELECT   userid,SUM(bonus) AS bonus
FROM
(
SELECT   ub_referrerid as userid,
         (ub_referrer_cashbonus ub_redeemer_cashbonus) as bonus
FROM     YOUR_TABLE_NAME
UNION ALL
SELECT   ub_redeemerid as userid,
         (ub_referrer_cashbonus ub_redeemer_cashbonus) as bonus
FROM     YOUR_TABLE_NAME
)        CASH
GROUP BY userid
ORDER BY userid;