lundi 23 février 2015

Query to return zero if nothing exist


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