please help me with query: I have following table sales:
customer material week value
customer1 material1 w1 100
customer1 material1 w2 200
customer1 material1 w4 300
customer1 material2 w4 200
And table weeks
week
w1
w2
w3
w4
I need to write query that returns table with "completed data". Result table must be:
customer material week value
customer1 material1 w1 100
customer1 material1 w2 200
customer1 material1 w3 0
customer1 material1 w4 300
customer1 material2 w1 0
customer1 material2 w2 0
customer1 material2 w3 0
customer1 material2 w4 200
I write this query but I think this is not optimal.
select
dict.customer,
dict.material,
weeks.week,
coalesce(sales.value, 0)
from
(select distinct
customer,
material
from
sales) dict
cross join
weeks
left join
sales on dict.customer = sales.customer and
dict.material = sales.material and
weeks.week = sales.week
Script for table initializations:
CREATE TABLE public.sales
(
customer character varying(10),
material character varying(18),
week character varying(3),
value numeric
);
CREATE TABLE public.weeks
(
week character varying(3)
);
insert into public.sales (customer, material, week, value)
values ('customer1', 'material1', 'w1', 100),
('customer1', 'material1', 'w2', 200),
('customer1', 'material1', 'w4', 300),
('customer1', 'material2', 'w4', 200);
insert into public.weeks (week)
values ('w1'), ('w2'), ('w3'), ('w4');
Thank you.
select
customer,
material,
week,
coalesce(sum(value), 0) as value
from
sales
right join (
(
select distinct customer, material
from sales
) s
cross join
weeks
) s using (customer, material, week)
group by 1,2,3
;
customer | material | week | value
-----------+-----------+------+-------
customer1 | material1 | w1 | 100
customer1 | material1 | w2 | 200
customer1 | material1 | w3 | 0
customer1 | material1 | w4 | 300
customer1 | material2 | w1 | 0
customer1 | material2 | w2 | 0
customer1 | material2 | w3 | 0
customer1 | material2 | w4 | 200
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With