Conditional aggregation
suggest changePayments Table
Customer | Payment_type | Amount |
—— | —— |———–| Peter | Credit | 100 | Peter | Credit | 300 | John | Credit | 1000 | John | Debit | 500 |
select customer,
sum(case when payment_type = 'credit' then amount else 0 end) as credit,
sum(case when payment_type = 'debit' then amount else 0 end) as debit
from payments
group by customer
Result:
Customer | Credit | Debit |
—— |———–|———| Peter | 400 | 0 | John | 1000 | 500 |
select customer,
sum(case when payment_type = 'credit' then 1 else 0 end) as credit_transaction_count,
sum(case when payment_type = 'debit' then 1 else 0 end) as debit_transaction_count
from payments
group by customer
Result:
Customer | credit_transaction_count| debit_transaction_count |
—— |———–|––––| Peter | 2 | 0 | John | 1 | 1 |
Found a mistake? Have a question or improvement idea?
Let me know.
Table Of Contents