條件聚合
付款表
顧客 | 支付方式 | 量 |
---|---|---|
彼得 | 信用 | 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 |