條件聚合

Created: November-22, 2018

付款表

顧客	支付方式	量
彼得	信用	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