Oracle: Get data on all months, 0 if no data

Very similar to exsiting answers, but this:

select months.month, mv.mycost, coalesce(mv.mynumber, 0) as mynumberfrom (  select to_char(date '1970-01-01'    + numtoyminterval(level - 1, 'month'), 'mm') as month  from dual  connect by level <= 12) monthsleft join myoracle_mv mvon mv.month = months.monthorder by months.month, mv.mycost, mv.mynumber;

gives this with the data you posted:

MONTH MYCOST   MYNUMBER----- ------ ----------01    AAA       3777.24 01    BBB         18811 01    CCC       3845.47 02    AAA      49973.12 02    BBB      29872.67 02    CCC       3050.54 03    AAA       4049.91 03    BBB      29068.55 03    CCC       3784.44 03    DDD        107.07 04    AAA       469.485 04    BBB      264957.8 04    CCC        799.73 04    DDD        181.78 05    AAA       5872.22 05    BBB         67673 05    CCC      124884.2 05    DDD        110.09 06    AAA      65837.71 06    BBB        855.02 06    CCC       5157.24 06    DDD      18016.19 07    AAA        566.23 07    BBB        5226.1 07    CCC      19184.78 07    DDD       1772.95 08    AAA      18432.95 08    BBB       2663.24 08    CCC       2280.05 08    DDD         63.32 09                    0 10                    0 11                    0 12    AAA       4337.75 12    BBB       5490.58  35 rows selected

If you want a zero to appear in the mynumber column then you can make that:

select months.month, mv.mycost, coalesce(mv.mynumber, 0) as mynumber

which gives:

...08    DDD         63.32 09                    0 10                    0 11                    0 12    AAA       4337.75 ...

From the comments on Jafar's answer it sounds like maybe you'd got that far on your own but you want zero values for all mycost values for all months. If that is the case then you need to get the list of possible values for mycost and outer join to that as well. This is taking all values that are in the MV already:

select months.month, costs.mycost, coalesce(mv.mynumber, 0) as mynumberfrom (  select to_char(date '1970-01-01'    + numtoyminterval(level - 1, 'month'), 'mm') as month  from dual  connect by level <= 12) monthscross join (  select distinct mycost  from myoracle_mv) costsleft join myoracle_mv mvon mv.month = months.monthand mv.mycost = costs.mycostorder by months.month, costs.mycost, mv.mynumber;

and gives:

MONTH MYCOST   MYNUMBER----- ------ ----------01    AAA       3777.24 01    BBB         18811 01    CCC       3845.47 01    DDD             0 02    AAA      49973.12 02    BBB      29872.67 02    CCC       3050.54 02    DDD             0 03    AAA       4049.91 03    BBB      29068.55 03    CCC       3784.44 03    DDD        107.07 04    AAA       469.485 04    BBB      264957.8 04    CCC        799.73 04    DDD        181.78 05    AAA       5872.22 05    BBB         67673 05    CCC      124884.2 05    DDD        110.09 06    AAA      65837.71 06    BBB        855.02 06    CCC       5157.24 06    DDD      18016.19 07    AAA        566.23 07    BBB        5226.1 07    CCC      19184.78 07    DDD       1772.95 08    AAA      18432.95 08    BBB       2663.24 08    CCC       2280.05 08    DDD         63.32 09    AAA             0 09    BBB             0 09    CCC             0 09    DDD             0 10    AAA             0 10    BBB             0 10    CCC             0 10    DDD             0 11    AAA             0 11    BBB             0 11    CCC             0 11    DDD             0 12    AAA       4337.75 12    BBB       5490.58 12    CCC             0 12    DDD             0  48 rows selected 

But hopefully you have another table that holds the possble mycost values (assuming that's representing something like a cost center, rather than a price; slightly hard to tell what's what) and you can use that instead of the subquery.

SQL Fiddle.

Also note that if you wanted to add a filter, e.g. to restrict data to a particular year, you'd need to do that in the in the left join clause, not as a where clause, or you'd revert the outer join to an inner one. For example, adding this:

where mv.year = 2011

would mean you only got back two rows:

MONTH MYCOST   MYNUMBER----- ------ ----------12    AAA       4337.75 12    BBB       5490.58 

But if you made than another condition on the outer join you'd still get 48 rows back, with 46 of them having zeros and two having the values above:

...left join myoracle_mv mvon mv.month = months.monthand mv.mycost = costs.mycostand mv.year = 2011order by months.month, costs.mycost, mv.mynumber;...11    CCC             0 11    DDD             0 12    AAA       4337.75 12    BBB       5490.58 12    CCC             0 12    DDD             0  48 rows selected 

You'd need to do an outer join between the two inline views

select MonthName, myCost, myNumber   from (select MONTH mm, myCost, myNumber          from myOracle_mv       ) myTotals       right outer join       (select to_char(date '2012-12-1' + numtoyminterval(level,'month'), 'mm') MonthName          from dual       connect by level <= 12) ALLMONTHS       on( myTotals.mm = allmonths.MonthName )

You can also use the old Oracle-specific (+) syntax for outer joins but I would generally suggest using the SQL standard syntax.

Maybe something like this

select MonthName, COALESCE(myCost,0), myNumber   from     (       select to_char(date '2012-12-1' + numtoyminterval(level,'month'), 'mm') MonthName        from dual      connect by level <= 12     ) ALLMONTHS LEFT OUTER JOIN     (       select MONTH mm, myCost, myNumber         from myOracle_mv     ) myTotals ON     mm = MonthName