CODEWITHSUNDEEP
mysqljoinsum
Hussein Al-maani
Hussein Al-maani

Reputation: 41

Multiple sum on different cols in same query

Is it 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 
     , t2.credit  
     , t1.ID 
     , t2.ID 
  FROM
     ( 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 
  JOIN 
     ( 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)
 GROUP  
    BY t1.ID

Please explain your answer.

Upvotes: 0

Views: 65

Answers (1)

spencer7593
spencer7593

Reputation: 108510

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
  GROUP 
     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 

  GROUP 
     BY t.id_account

Upvotes: 1

Related Questions