CODEWITHSUNDEEP
mysql
Priyamanu
Priyamanu

Reputation: 183

Mysql sum column according to another column

This is my table data from my Mysql Table

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 my result like 

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

where rec_Amount is sum of t_acd_Id and pay_Amount is sum of t_acc_Id

How to get this result?

My current query 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 multiple rows

Upvotes: 4

Views: 57

Answers (3)

Amit Ramoliya
Amit Ramoliya

Reputation: 392

can you try with this query? as i have 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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

The following query takes into account the possibility that a given idea might appear only in t_acd_Id or t_acc_Id, but not both. In this case, we would like to do a full outer join, but MySQL does not directly support this. Instead, the first subquery in my answer obtains all unique ID values. This is then LEFT JOINed to two other 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 for a running demo:

SQLFiddle

Upvotes: 0

1000111
1000111

Reputation: 13519

This query only serves the above requirement (for single account). If you want to get result for all the accounts then you need to group the records by account.

Try this(It's based 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

Upvotes: 1

Related Questions