SQL Aggregazione condizionale


Esempio

Tabella dei pagamenti

Cliente Modalità di pagamento Quantità
Peter Credito 100
Peter Credito 300
John Credito 1000
John Addebito 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

Risultato:

Cliente Credito Addebito
Peter 400 0
John 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

Risultato:

Cliente credit_transaction_count debit_transaction_count
Peter 2 0
John 1 1