I have a table With currency
, paymenttype
and invoiceamount
. I have to write a query to get currency
, paymenttype
and total invoiceamount
made.
This is pretty simple with group by. But actually I have three payment types - 0, 1, 2. The data in the table is
Currency PaymentType Invoice Amount
Aaa. 0. 100
Aaa. 1. 200
Aaa. 1. 50
Bbb. 0. 150
Bbb. 1. 100
Bbb. 2. 100
My query is
Select currency, paymenttype, sum(invoiceamount) as total
from table
group by currency, paymenttype
Result
Currency paymenttype total
Aaa. 0. 100
Aaa. 1. 250
Bbb. 0. 150
Bbb. 1. 100
Bbb. 2. 100
As Aaa. does not have paymenttype
2 it should also show a row with 0 value like below.
Aaa. 2. 0
How to do this?
Thanks in advance
Aucun commentaire:
Enregistrer un commentaire