SQL Functions (Aggregate) Conditional aggregation
Example
Payments 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 |
