SQL Functions (Aggregate) Conditional aggregation

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

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


Got any SQL Question?