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

Question

I have an Mview that brings data group by `idNumber` and `Month`. So I want to display 0 if there is no data for an specific month. This is my query: ``` select MonthName, myCost, myNumber from ( select MONTH mm, myCost, myNumber from myOracle_mv ) myTotals, ( select to_char(date '2012-12-1' + numtoyminterval(level,'month'), 'mm') MonthName from dual connect by level <= 12 ) ALLMONTHS where mm = MonthName ``` So I was expecting: ``` Month Number Data ----------------------- 1 abc123 4444 2 0 3 abc123 4444 4 abc123 4444 5 0 6 abc123 4444 7 abc123 4444 8 0 9 abc123 4444 10 abc123 4444 11 0 12 abc123 4444 ``` Instead I'm still getting: ``` 1 abc123 4444 3 abc123 4444 4 abc123 4444 6 abc123 4444 7 abc123 4444 9 abc123 4444 10 abc123 4444 12 abc123 4444 ``` Any ideas? Thanks! EDIT: Thanks for the answers. I did have the outer join in my Query but forgot to type in because was concentrating in changes the names of the table/columns. So yes, I have tried with the OUTER JOIN and I still not g…

Accepted Answer

Very similar to exsiting answers, but this:

```
select months.month, mv.mycost, coalesce(mv.mynumber, 0) as mynumber
from (
  select to_char(date '1970-01-01'
    + numtoyminterval(level - 1, 'month'), 'mm') as month
  from dual
  connect by level <= 12) months
left join myoracle_mv mv
on mv.month = months.month
order 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 mynumber
from (
  select to_char(date '1970-01-01'
    + numtoyminterval(level - 1, 'month'), 'mm') as month
  from dual
  connect by level <= 12) months
cross join (
  select distinct mycost
  from myoracle_mv) costs
left join myoracle_mv mv
on mv.month = months.month
and mv.mycost = costs.mycost
order 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 mv
on mv.month = months.month
and mv.mycost = costs.mycost
and mv.year = 2011
order 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 
```

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

Solution

Problem