how to subtract same column of row where type is different?

Question

I want to subtract rows of same table where (where clause is different ).

SELECT SUM(`balance`) AS `balance` FROM `vouchers`  WHERE `type`='CP' AND `post_status`='yes' GROUP BY `interactive_person`;

the above query give me desirable result for first kinda rows! the second query is

SELECT SUM(`balance`) AS `balance` FROM `vouchers`  WHERE `type`='CR' AND `post_status`='yes' GROUP BY `interactive_person`;

These query return more then one row grouped by interactive person i want that it subtract record where interactive person(is a number) is same but type is change. in short its like CashReceipt - CashPayment. I used first query as a subquery of second but error shown that subquery return more then one row. i need help to solve this !

Answer 1

You could use case to calculate your sum() in same query

SELECT 
SUM(CASE WHEN `type`='CP' THEN `balance` ELSE 0 END) AS CP_balance,
SUM(CASE WHEN `type`='CR' THEN `balance` ELSE 0 END) AS CR_balance,
SUM(CASE WHEN `type`='CP' THEN `balance` WHEN `type`='CR' THEN  -1 * `balance` ELSE 0 END) AS diff
FROM `vouchers`  
WHERE `post_status`='yes' 
GROUP BY `interactive_person`;