stock_mov.sql

select name, unit_price, sum(quantity), unit_price*sum(quantity)
from order_items
         inner join products p on p.id = order_items.product_id
         inner join orders o on o.id = order_items.order_id
group by unit_price, name, product_id, order_type having product_id = 1 and order_type = 'Purchase';

select name, unit_price, sum(quantity) qty, sum(total_price) amount, (sum(total_price))-(1800*sum(quantity)) benefice
from order_items
         inner join products p on p.id = order_items.product_id
         inner join orders o on o.id = order_items.order_id
group by unit_price, name, product_id, order_type, order_date::date having order_date::date between '2020-09-30' and '2020-10-06' and product_id = 1 and order_type = 'Sale';


-- Alveol
select name, unit_price, sum(quantity) qty, sum(total_price) amount, (sum(total_price))-(1800*sum(quantity)) benefice
from order_items
         inner join products p on p.id = order_items.product_id
         inner join orders o on o.id = order_items.order_id
group by unit_price, name, product_id, order_type having product_id = 1 and order_type = 'Sale';

-- Detail
select name, unit_price, sum(quantity) qty, sum(total_price) amount, (sum(total_price))-(1800*sum(quantity)) benefice
from order_items
         inner join products p on p.id = order_items.product_id
         inner join orders o on o.id = order_items.order_id
group by unit_price, name, product_id, order_type having product_id = 6 and order_type = 'Sale';

-- Casse
select name, unit_price, sum(quantity) qty, sum(total_price) amount, (sum(total_price))-(1800*sum(quantity)) benefice
from order_items
         inner join products p on p.id = order_items.product_id
         inner join orders o on o.id = order_items.order_id
group by unit_price, name, product_id, order_type having product_id = 4 and order_type = 'Sale';

select *
from order_items inner join orders o on o.id = order_items.order_id where product_id = 1 and order_type = 'Sale' and unit_price < 1800;