oracle - SUM using 3 tables and OUTER join -

having issues sum based on joins between 3 tables. in example below, ref contains 11 entries per month. errors aggregate columns besides error_count. items aggregate columns besides item_count. key between errors , items id , month year. key between ref , errors error_code , month_year. key between ref , items month_year.

i'd see entry in result builds on each original entry in ref i.e. 11 entries per month_year, per id. running below, no entry created when match between ref , errors not found, total_error_count output 0 in case, output actual sum in total_item_count based on join items.

i have 10 rows missing id = 1 , 27 missing id = 2. please advise, everyone.

sql fiddle

oracle 11g r2 schema setup:

create table ref(   month_year varchar2(6),   error_code number(2) ) /  create table errors(   id number(18),   month_year varchar2(6),   error_code number(2),   include_ind varchar2(1),   exclude_ind varchar2(1),   error_count number   ) /  create table items(   id number(18),   month_year varchar2(6),   include_ind varchar2(1),   exclude_ind varchar2(1),   item_count number   ) / create table result(   id number(18),   error_code number(2),   partition varchar2(10),   month_year varchar2(6),   total_error_count number,   total_item_count number,   rate number,   query_timestamp varchar2(19) ) / insert ref(month_year,error_code) values ('201212','11'); insert ref(month_year,error_code) values ('201212','12'); insert ref(month_year,error_code) values ('201212','13'); insert ref(month_year,error_code) values ('201212','14'); insert ref(month_year,error_code) values ('201212','16'); insert ref(month_year,error_code) values ('201212','17'); insert ref(month_year,error_code) values ('201212','3'); insert ref(month_year,error_code) values ('201212','4'); insert ref(month_year,error_code) values ('201212','5'); insert ref(month_year,error_code) values ('201212','6'); insert ref(month_year,error_code) values ('201212','8'); insert ref(month_year,error_code) values ('201301','11'); insert ref(month_year,error_code) values ('201301','12'); insert ref(month_year,error_code) values ('201301','13'); insert ref(month_year,error_code) values ('201301','14'); insert ref(month_year,error_code) values ('201301','16'); insert ref(month_year,error_code) values ('201301','17'); insert ref(month_year,error_code) values ('201301','3'); insert ref(month_year,error_code) values ('201301','4'); insert ref(month_year,error_code) values ('201301','5'); insert ref(month_year,error_code) values ('201301','6'); insert ref(month_year,error_code) values ('201301','8'); insert ref(month_year,error_code) values ('201302','11'); insert ref(month_year,error_code) values ('201302','12'); insert ref(month_year,error_code) values ('201302','13'); insert ref(month_year,error_code) values ('201302','14'); insert ref(month_year,error_code) values ('201302','16'); insert ref(month_year,error_code) values ('201302','17'); insert ref(month_year,error_code) values ('201302','3'); insert ref(month_year,error_code) values ('201302','4'); insert ref(month_year,error_code) values ('201302','5'); insert ref(month_year,error_code) values ('201302','6'); insert ref(month_year,error_code) values ('201302','8'); insert items(id,month_year,include_ind,exclude_ind,item_count) values ('1','201212','y','n','30'); insert items(id,month_year,include_ind,exclude_ind,item_count) values ('1','201301','y','n','30'); insert items(id,month_year,include_ind,exclude_ind,item_count) values ('1','201302','y','n','30'); insert items(id,month_year,include_ind,exclude_ind,item_count) values ('2','201212','y','n','30'); insert items(id,month_year,include_ind,exclude_ind,item_count) values ('2','201301','y','n','30'); insert items(id,month_year,include_ind,exclude_ind,item_count) values ('2','201302','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201212','3','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201212','4','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201212','5','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201212','6','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201212','11','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201212','12','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201212','13','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201212','14','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201301','3','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201301','5','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201301','6','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201301','11','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201301','12','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201301','13','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201301','14','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201302','3','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201302','4','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201302','5','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201302','6','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201302','11','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201302','12','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201302','13','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('1','201302','14','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('2','201212','3','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('2','201212','6','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('2','201301','3','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('2','201301','6','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('2','201302','3','y','n','30'); insert errors(id,month_year,error_code,include_ind,exclude_ind,error_count) values ('2','201302','6','y','n','30'); 

query 1:

begin  insert result    select          errors.id,         ref.error_code,         to_char(sysdate,'yyyy-mm-dd'),         ref.month_year,         sum(errors.error_count),         sum(items.item_count),         sum(errors.error_count) / sum(items.item_count),         to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')    ref         right outer join errors         on ref.error_code = errors.error_code         , ref.month_year = errors.month_year            right outer join items            on items.id = errors.id            , items.month_year = errors.month_year   errors.include_ind = 'y' ,     errors.exclude_ind = 'n' group           errors.id,         ref.error_code,         ref.month_year,         sysdate; end; 

[results][2]:

query 2:

select  *    result order   id, error_code, month_year 

[results][3]:

| id | error_code |  partition | month_year | total_error_count | total_item_count | rate |     query_timestamp | ----------------------------------------------------------------------------------------------------------------- |  1 |          3 | 2013-03-13 |     201212 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |          3 | 2013-03-13 |     201301 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |          3 | 2013-03-13 |     201302 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |          4 | 2013-03-13 |     201212 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |          4 | 2013-03-13 |     201302 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |          5 | 2013-03-13 |     201212 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |          5 | 2013-03-13 |     201301 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |          5 | 2013-03-13 |     201302 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |          6 | 2013-03-13 |     201212 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |          6 | 2013-03-13 |     201301 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |          6 | 2013-03-13 |     201302 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |         11 | 2013-03-13 |     201212 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |         11 | 2013-03-13 |     201301 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |         11 | 2013-03-13 |     201302 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |         12 | 2013-03-13 |     201212 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |         12 | 2013-03-13 |     201301 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |         12 | 2013-03-13 |     201302 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |         13 | 2013-03-13 |     201212 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |         13 | 2013-03-13 |     201301 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |         13 | 2013-03-13 |     201302 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |         14 | 2013-03-13 |     201212 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |         14 | 2013-03-13 |     201301 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  1 |         14 | 2013-03-13 |     201302 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  2 |          3 | 2013-03-13 |     201212 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  2 |          3 | 2013-03-13 |     201301 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  2 |          3 | 2013-03-13 |     201302 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  2 |          6 | 2013-03-13 |     201212 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  2 |          6 | 2013-03-13 |     201301 |                30 |               30 |    1 | 2013-03-13 06:43:16 | |  2 |          6 | 2013-03-13 |     201302 |                30 |               30 |    1 | 2013-03-13 06:43:16 | 

you can take profit of coalesce(a,b) replace null values of b. then, outer joins, have enough rows

select it.id,     r.month_year,     r.error_code,     coalesce(sum(error_count),0) "total errors",     sum(item_count) "total items",     coalesce(sum(error_count),0)/sum(item_count) "rate" items     join ref r         on (it.month_year = r.month_year)     left outer join errors er         on (r.month_year = er.month_year             , er.error_code = r.error_code             , er.id = it.id) group it.id,      r.month_year,      r.error_code order it.id,     r.month_year,     r.error_code