could sql create view with fix group even without data

Question

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?

Answer 1

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

Answer 2

Create a table for the days of the week, including numbering (Sunday may be day 1 or day 7).

-- first day of the week: Sunday
create table days 
as
select 1 daynumber, 'Sunday' weekday from dual 
union select 2, 'Monday' from dual
union select 3, 'Tuesday' from dual
union select 4, 'Wednesday' from dual
union select 5, 'Thursday' from dual
union select 6, 'Friday' from dual
union select 7, 'Saturday' from dual ;

Then, use an outer join, plus a case expression in the SELECT, and order according to your requirements.

select
  D.weekday
, case 
    when sum(E.qty_) is null then 0
    else sum(E.qty_)
  end total
from existing E
  right join days D on E.day_ = D.weekday 
group by D.weekday, D.daynumber
order by D.daynumber ;

-- output:
WEEKDAY        TOTAL
--------- ----------
Sunday            27
Monday             5
Tuesday           20
Wednesday          0
Thursday           0
Friday             0
Saturday           0

Dbfiddle here

Create a view (without using an additional table):

create or replace view new_view
as
select
  D.weekday
, case 
    when sum(E.qty_) is null then 0
    else sum(E.qty_)
  end total
from existing E
  right join ( 
    select 1 daynumber, 'Sunday' weekday from dual 
    union select 2, 'Monday' from dual
    union select 3, 'Tuesday' from dual
    union select 4, 'Wednesday' from dual
    union select 5, 'Thursday' from dual
    union select 6, 'Friday' from dual
    union select 7, 'Saturday' from dual 
  ) D
    on E.day_ = D.weekday 
group by D.weekday, D.daynumber
order by D.daynumber ;

Testing: see dbfiddle.