條件聚合

付款表

顧客 支付方式
彼得 信用 100
彼得 信用 300
約翰 信用 1000
約翰 借方 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

結果:

顧客 信用 借方
彼得 400 0
約翰 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

結果:

顧客 credit_transaction_count debit_transaction_count
彼得 2 0
約翰 1 1