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

Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm...
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } ...
Read more

ruby on rails - Seeing duplicate requests handled with Unicorn -

recently upgraded our ec2 instances c3.large in order boost more unicorn workers handle increased traffic our site. (6xworkers per machine - 2 ec2 instances). when did this, started seeing duplicate records being created! looks somehow, maybe 2 unicorn workers attempting process same request? in nginx logs see 1 [post] request particular rails controller - yet 2 records being created in database. i'm hitting kind of race condition issue - have no idea how debug this. doesn't happen time, when it's frustrating well. noticed 2 database records within 5 seconds of each other. (and yes disable button when user clicks submit button). unicorn 4.8.2 ruby 1.9.3 rails 3.2.14 any great! thanks! although may not fix problem, upgrade ruby 2.0.0. there blog mentioning idea upgrade ruby 2.0.0 if using unicorn. for example: https://www.digitalocean.com/community/articles/how-to-optimize-unicorn-workers-in-a-ruby-on-rails-app specifically: if using ruby 1.9, ...
Read more