Mysql sum column according to another column

Question

This is my table data from table Mysql

t_Id  t_Type  t_Date      t_acd_Id  t_acc_Id  t_Amount  t_Desc  t_u_Id    c_Id  
------  ------  ----------  --------  --------  --------  ------  ------  --------
     1       0  2016-01-26       266        29  400.00                 1         1
     2       0  2016-01-27       266        29  160.00                 1         1
     3       1  2016-01-28        29       266  83.30                  1         1
     4       2  2016-01-27        29       272  400.00                 1         1
     5       0  2016-01-27       266       272  300.00                 1         1
     6       1  2016-01-28       272        22  20.00                  1         1

I want to get the result, for example

     accout_Id  rec_Amount  pay_Amount 
        ------  ----------  ----------  
            29      483.30      560.00 

where rec_Amountis the sum t_acd_Idand pay_Amountis the sumt_acc_Id

How to get this result?

My current request is SELECT (SELECT SUM(t_Amount) FROM tbl_transactions WHEREt_acd_Id = 29) AS rec_Amount, (SELECT SUM(t_Amount) FROM tbl_transactions WHEREt_acc_Id= 29) AS pay_Amount FROM tbl_transactions

which gives a few lines

Answer 1

Can you try with this query? as I checked it manually.

SELECT t4.t_acd_Id as accout_Id ,sum(t4.t_Amount) as rec_Amount, (SELECT SUM(t_Amount) from table4 WHERE t_acc_Id =t4.t_acd_Id) as pay_Amount FROM `table4` as t4 WHERE t4.t_acd_Id IN (29,266) GROUP BY t4.t_acd_Id

thanks

Answer 2

This request only serves the specified requirement (for a separate account). If you want to get the result for all accounts, you need to group the records by account.

Try it (it depends on your requirement):

SELECT CASE 
         WHEN t_acc_id = 29 THEN t_acc_id 
         WHEN t_acd_id = 29 THEN t_acd_id 
       END      account_id, 
       Sum(CASE 
             WHEN t_acd_id = 29 THEN t_amount 
             ELSE 0 
           END) rec_Amount, 
       Sum(CASE 
             WHEN t_acc_id = 29 THEN t_amount 
             ELSE 0 
           END) pay_Amount 
FROM   tbl_transactions 
WHERE  t_acc_id = 29 
        OR t_acd_id = 29 

Answer 3

The following query takes into account the possibility that this idea may appear only in t_acd_Idor t_acc_Id, but not in both. In this case, we would like to make a full external connection, but MySQL does not directly support this. Instead, the first subquery in my answer gets all the unique ID values. This is then LEFT JOINed for the other two subqueries for each of the totals you want.

SELECT t1.accout_Id, t2.rec_Amount, t3.pay_Amount
FROM
(
    SELECT DISTINCT t_acd_Id AS accout_Id FROM tbl_transactions
    UNION
    SELECT DISTINCT t_acc_Id AS accout_Id FROM tbl_transactions
) t1
LEFT JOIN
(
    SELECT t_acd_Id AS accout_Id, SUM(t_acd_Id) AS rec_Amount
    FROM tbl_transactions
    GROUP BY t_acd_Id
) t2
ON t1.accout_Id = t2.accout_Id
LEFT JOIN
(
    SELECT t_acc_Id AS accout_Id, SUM(t_acc_Id) AS pay_Amount
    FROM tbl_transactions
    GROUP BY t_acc_Id
) t3
ON t1.accout_Id = t3.accout_Id

Click the link below to launch the demo:

SQLFiddle