SQL Substract 2 colums in same table

I’ve table named ‘detail_transaksi’. I want to SUM all of the data in “jumlah” column where “debit_kredit” = ‘D’ which is grouped in the same “kode_akun” data. Also, I want to SUM all of the data in “jumlah” column where “debit_kredit” = ‘K’ which is grouped in the same “kode_akun” data. Then, I want to SUBSTRACT both of them. How can I do it in SQL or maybe in PHP?

Here’s columns in my table:

id
kode_transaksi
kode_akun
debit_kredit
jumlah

Answer

You can use conditional aggregation:

select kode_akun,
       sum(case when debit_kredit = 'K' then jumlah
                when debit_kredit = 'D' then - jumlah
           end)
from detail_transaksi
group by kode_akun;