Hussien Al-maani Hussien Al-maani - 2 years ago 73
MySQL Question

Multiple sum on different cols in same query

Goodnight everybody ,,, if is possible to sum on cols if inner join is true and sum on another cols if another inner join true i.e

SELECT t1.debit
, t1.ID
, t2.ID
( SELECT SUM(booking_value) debit
, gl_acct.id_fin_gl_acct ID
FROM bookings
JOIN gl_acct
ON (CONCAT('1',gl_acct.id_fin_gl_acct) = bookings.id_debit_account)
) t1
( SELECT SUM(booking_value) credit
, gl_acct.id_fin_gl_acct ID
FROM bookings
JOIN gl_acct
ON (CONCAT('1',gl_acct.id_fin_gl_acct)=bookings.id_credit_account)
) t2
ON (t1.ID = t2.ID)
BY t1.ID

and please explain you answer

thanks so much

Answer Source

An example of conditional aggregation.

 SELECT t.id_account
      , SUM(IF(t.cr_or_db='cr',t.tot_booking_value,0)) AS `tot_credit` 
      , SUM(IF(t.cr_or_db='db',t.tot_booking_value,0)) AS `tot_debit`
   FROM ( 
          SELECT 'cr'                  AS `cr_or_db`
               , c.id_credit_account   AS `id_account`
               , SUM(c.booking_value)  AS `tot_booking_value`
            FROM bookings c
           GROUP BY c.id_credit_account
           UNION ALL
          SELECT 'db'                  AS `cr_or_db`
               , d.id_debit_account    AS `id_account`
               , SUM(d.booking_value)  AS `tot_booking_value`
            FROM bookings d
           GROUP BY d.id_debit_account
        ) t
     BY t.id_account 

The inline view t gets us total credits for each id_credit_account and total debits for each id_debit_account.

The original query joins both of those id_ columns to the same account table, so we're assuming those are both foreign keys to that other table, which means they are the same datatype...

A join operation would only be required if there is some reason we need to include the gl_acct table. Assuming that id_fin_gl_acct is UNIQUE in gl_acct... we could add the join operation before the GROUP BY clause.

        ) t

   JOIN gl_acct g
     ON CONCAT('1',g.id_fin_gl_acct) = t.id_account 

     BY t.id_account 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download