How could i create a view and group - and average - values from original table?-CodePudding

Let’s say I have a table with 3 columns (weekday, price1 and price2), like this:

create table original_table 
(weekday VARCHAR(15), 
price1 number (6), 
price2 number(6));

insert into original_table values (‘tuesday’, 12, 23);
insert into original_table values (‘monday’, 23, 45);
insert into original_table values (‘friday’, 45, 21);
insert into original_table values (‘friday’, 231, 34);
insert into original_table values (‘monday’, 35, 53);
insert into original_table values (‘tuesday’, 213, 9);

And I want to create a view from this original table grouping and averaging the prices (Price1 and Price2) by a specific column (Weekday). Leaving something like this:

Code I am using to create the view and averaging the prices. Faraway from my expected output, but it's to kick off.

create view view_from_original_table as
weekday, avg_price1, avg_price2
select weekday, avg(price1), avg(price2)
from original_table
group by weekday;

I think that with pivot I could achieve this result, but I’m having trouble understanding it.

CodePudding user response：

you could do it with a case statement

here is the fiddle https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e9f1bec3e9da3319a31a92eb9aa11b11

with t as (select weekday, avg(price1) p1 , avg(price2) p2
from original_table
group by weekday)
select  t.weekday, 
case t.weekday when 'monday' then p1 end as Monday_avg1,
case t.weekday when 'monday' then p2 end as Monday_avg2,
case t.weekday when 'tuesday' then p1 end as Tuesday_avg1,
case t.weekday when 'tuesday' then p2 end as Tuesday_avg2,
case t.weekday when 'friday' then p1 end as Friday_avg1,
case t.weekday when 'friday' then p2 end as Friday_avg2
from t
order by case weekday when 'monday' then 1 when 'tuesday' then 2 else 3 end