CODEWITHSUNDEEP
mysqlsql
Juggernaut
Juggernaut

Reputation: 806

How to find the aggregate total sum of debit - sum of credit and group in MYSQL?

I have a SQL Table with the following rows. I am trying to find the aggregate sum of each book_name with the formula, (sum of debit- sum of credit).

Thus this is the table,

book_name transaction_type  amount
demo       credit            20
demo       debit             100
testing    credit            10
testing    debit             30

This is the result I want.

demo -80
testing -20

How do I do this query in MySQL? I have been trying various syntaxes and solutions but none seem to work.

Upvotes: 0

Views: 637

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

One method is to use conditional aggregation:

select book_name, sum(case when transaction_type = 'credit' then amount else - amount end)
from t
where transaction_type in ('credit', 'debit')
group by book_name;

Upvotes: 6

Related Questions