Reputation: 477
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
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
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
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
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
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