Could sql create view with fix group even without data

oracleview

Let say that I have a table as below :

day       | Qty
---------------
Sunday    | 10
Monday    | 5
Tuesday   | 20
Sunday    | 17

as I have another table that day value could only be 'Sunday' till 'Saturday', I would like to have a resume table as below:

day       | Sum(Qty)
---------------
Sunday    | 27
Monday    | 5
Tuesday   | 20
Wednesday | 0
Thursday  | 0
Friday    | 0
Saturday  | 0

The value for Wednesday till Saturday have zero value because of no data. Is there any solution?

Best Answer

create table t1(day varchar2(20),qty number);
create table t2(day varchar2(20));
insert into t1 values('Sunday',10)
insert into t1 values('Monday', 5);
insert into t1 values('Tuesday', 20)
insert into t1 values('Sunday',17)
insert into t2 select trim(to_char(sysdate+level,'Day')) WeekDay from dual connect by level<=7;
select t2.day, nvl(sum(t1.qty),0) Sum_qty from t1 right outer join t2 on(t1.day=t2.day) group by t2.day;
DAY       | SUM_QTY
:-------- | ------:
Tuesday   |      20
Sunday    |      27
Monday    |       5
Wednesday |       0
Thursday  |       0
Saturday  |       0
Friday    |       0

OR

create view sum_table as
select t2.day, 
       nvl(sum(t1.qty),0) Sum_qty 
from t1 
right outer join 
       (select trim(to_char(sysdate+level,'Day')) Day 
                from dual 
                connect by level<=7)t2 
on(t1.day=t2.day) 
group by t2.day;

dbfiddle here