CODEWITHSUNDEEP
mysqlsql
Prajith A S
Prajith A S

Reputation: 477

mysql table data output in a single row based on the type of the data

I have the following data in mysql table called wallet_txns.

wlt_name   wlt_txn_type   wlt_txn_amount
A           Income              200
A           Expense             100 
B           Income              100
B           Income              500
B           Expense             200

I trying to get the output of the data like below ( the sum of income and expense in a single row)

Wlt_name    Expense       Income
A           100            200
B           200            600

I have used the following query, But i am not getting the output as expected, (the income and expense in getting in seperate rows) Please help...

select
    wlt_name,
    if(wlt_txn_type = 'Expense', wlt_txn_amount, 0) as Expense,
    if(wlt_txn_type = 'Income', wlt_txn_amount, 0) as Income
from wallet_txns
;

Upvotes: 0

Views: 67

Answers (5)

AGI_rev
AGI_rev

Reputation: 131

I'm not a mysql user, but TSQL has little secrets to me. Check for PIVOT - UNPIVOT alternatives on the WWW.

Upvotes: 0

cn0047
cn0047

Reputation: 17091

You already almost wrote correct query, but you forget about sum and group. Here query:

select
    wlt_name,
    sum(if(wlt_txn_type = 'Expense', wlt_txn_amount, 0)) as Expense,
    sum(if(wlt_txn_type = 'Income', wlt_txn_amount, 0)) as Income
from wallet_txns
group by wlt_name
;

Upvotes: 0

ZI TEG
ZI TEG

Reputation: 63

Maybe you can help this part of the code: SUM(IF(wlt_txn_type = "Income", wlt_txn_amount, 0)) AS IncomeTotal.

Upvotes: 1

Arion
Arion

Reputation: 31249

You could do something like this:

SELECT
    Wlt_name,
    SUM(CASE WHEN wlt_txn_type='Expense' THEN wlt_txn_amount ELSE 0 END) AS Expense,
    SUM(CASE WHEN wlt_txn_type='Income' THEN wlt_txn_amount ELSE 0 END) AS Income
FROM
    wallet_txns
GROUP BY
    Wlt_name

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

You can use conditional sum and then group by

select 
wlt_name, 
sum(
 case when wlt_rxn_type ='Income' then wlt_txn_amount else 0 end
) as `Income`,
sum(
 case when wlt_rxn_type ='Expense' then wlt_txn_amount else 0 end
) as `Expense` 
from wallet_txns group by wlt_name;

Upvotes: 0

Related Questions