I have three tables: super_consumers , balance_ins, recharges

super_consumers table:

id            name
----------    ----
1              A
2              B

balance_ins table:

id    super_consumer_id  amount   type(1,2)
---   -----------------  ------   ----
1            1             10       1
2            1             20       1
3            2             10       2

recharges table:

id    super_consumer_id  amount  status(0,1)
---   -----------------  ------  ------
1            1             5       1
2            1             10      1
3            2             15      0

I want to get each super_consumers table’s Total Balance In amount and Total recharge amount by following some condition below,

1. Total_balance_in amount from balance_in table where type == 1
2. Total_recharge amount from recharges table where status == 1

Expected result :

[
    'id' = 1,
    'name' = 'A',
    'total_balance_in' = 30,
    'total_recharge' = 15
],
[
    'id' = 2,
    'name' = 'B',
    'total_balance_in' = 0,
    'total_recharge' = 0
]

How can I properly write this Query in 'laravel DB Query' or 'mysql' ?

CodePudding user response：

Try:

select super_consumers.id ,
       super_consumers.name,
       IF(tot_balance_amount IS NULL, 0 , tot_balance_amount) as tot_balance_amount,
       IF(tot_recharge_amount IS NULL ,0, tot_recharge_amount) as tot_recharge_amount
from  super_consumers 
left  join (select super_consumer_id,sum(amount) as tot_balance_amount  from balance_ins  where type='1' group by super_consumer_id ) as a 
on  super_consumers.id=a.super_consumer_id
left join (select super_consumer_id,sum(amount) as tot_recharge_amount  from recharges  where `status`='1' group by super_consumer_id ) as b 
on  super_consumers.id=b.super_consumer_id;

Demo:

CodePudding user response：

Query:

SELECT
    c.id
,   c.name
,   ifNull(sum(res.balance), 0) as balance_sum
,   ifNull(sum(res.recharge), 0) as recharge_sum
FROM
    super_consumers as c
    LEFT JOIN 
    (
      SELECT
        b.super_consumer_id
      , b.amount as balance
      , 0 as recharge
      FROM
        balance_ins as b
      WHERE
        b.type = 1

      UNION

      SELECT
        r.super_consumer_id
      , 0 as balance
      , r.amount as recharge
      FROM
        recharges as r
      WHERE
        r.status = 1
    ) as res
    ON
        c.id = res.super_consumer_id
GROUP BY
    c.id
,   c.name

Result: