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?

If the list of payment types and currencies are known, you can do this with CTEs.

with paymenttypes as (
  select 0 as ptype,
  Union select 1 as ptype,
  Union select 2 as ptype ), 
currencies as 
( select 'Aaa' as currency
  union
  select 'Bbb' as currency ),
sourcedata as (
  select currency, ptype as paymenttype, 0 as amount
  from currencies, paymenttypes
  Union all
  select * 
  from data)
select currency, paymenttype, sum(amount)
from sourcedata
group by currency, paymenttype;

It basically creates a Cartesian product of all currency, paymenttype pairs with a 0 amount, then joins it with the source data.

If your lists of payment types and currencies are all in lookup tables, then substitute them in.

Also note there are other ways, using COALESCE etc.

SQL Fiddle link,