SQL query to derive average sale of an item-CodePudding

Below are two tables. An item is being sold at different values during different periods. I need to calculate average sale of the item. I need to come to an optimal working query. I think the trick would be to map each sales date with their start_date and end_date and multiply the quatity with their respective price for that period and then derive the avergae using the formula total sales amount/ total quatity.

CREATE TABLE sales( item_id varchar(255) , start_date date , end_date date, price int ) ;

insert into sales values ('mobile','2021-01-01','2021-01-05',500), ('mobile','2021-01-06','2021-01-09',400), ('mobile','2021-01-10','2021-01-15',900) ;

CREATE TABLE item( sales_id int, item_id varchar(255) , sales_date date , quatity int ) ;

insert into item values (101,'mobile','2021-01-01',10), (102,'mobile','2021-01-06',20), (103,'mobile','2021-01-15',30) ;

CodePudding user response：

            select item_id, sum(total_amount)/sum(quatity) avg_price from
            (
            select s.item_id, s.price, i.quatity, s.price * i.quatity as total_amount
            from sales s, item i where i.item_id = s.item_id
            and i.sales_date between s.start_date and s.end_date
            ) as A
            group by item_id;

CodePudding user response：

Please try this,

SELECT 
i.item_id,avg(s.price*i.quatity) FROM 
item I 
LEFT JOIN 
sales S
on
I.item_id=S.item_id
AND I.sales_date >=S.start_date AND I.sales_date<=S.end_date
group by i.item_id;