Looking for sql Answers? Try Ask4KnowledgeBase
Looking for sql Keywords? Try Ask4Keywords

SQL Conditional aggregation


Example

Payments Table

CustomerPayment_typeAmount
PeterCredit100
PeterCredit300
JohnCredit1000
JohnDebit500
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:

CustomerCreditDebit
Peter4000
John1000500
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:

Customercredit_transaction_countdebit_transaction_count
Peter20
John11