how can you compare the sum of values of products in postresql from and last week on the same query ? for example i have this the first columns shows the sum of all milkshakes in the day J and the one after it j-7 and the third one is to show the evolution ?

CodePudding user response：

Create sample tables and insert sample data:

CREATE TABLE products (
    id serial4 NOT NULL,
    product_name varchar(200) NOT NULL,
    CONSTRAINT products_pk PRIMARY KEY (id)
);

INSERT INTO products (id, product_name) VALUES(125, 'Product 1');
INSERT INTO products (id, product_name) VALUES(163, 'Product 2');
INSERT INTO products (id, product_name) VALUES(209, 'Product 5');
INSERT INTO products (id, product_name) VALUES(194, 'Product 8');

CREATE TABLE orders (
    id serial4 NOT NULL,
    product_id int4 NOT NULL,
    action_date date NOT NULL,
    insert_date timestamp NOT NULL DEFAULT now(),
    quantity int4 NOT NULL,
    CONSTRAINT orders_pk PRIMARY KEY (id)
);

INSERT INTO orders (product_id, action_date, quantity) VALUES(125, '2022-02-25', 5);
INSERT INTO orders (product_id, action_date, quantity) VALUES(163, '2022-02-25', 12);
INSERT INTO orders (product_id, action_date, quantity) VALUES(209, '2022-02-22', 11);
INSERT INTO orders (product_id, action_date, quantity) VALUES(194, '2022-02-25', 24);
INSERT INTO orders (product_id, action_date, quantity) VALUES(163, '2022-02-20', 40);
INSERT INTO orders (product_id, action_date, quantity) VALUES(163, '2022-02-19', 20);
INSERT INTO orders (product_id, action_date, quantity) VALUES(163, '2022-02-01', 35);
INSERT INTO orders (product_id, action_date, quantity) VALUES(125, '2022-02-21', 5);
INSERT INTO orders (product_id, action_date, quantity) VALUES(125, '2022-02-11', 30);
INSERT INTO orders (product_id, action_date, quantity) VALUES(163, '2022-02-24', 10);
INSERT INTO orders (product_id, action_date, quantity) VALUES(194, '2022-02-23', 25);
INSERT INTO orders (product_id, action_date, quantity) VALUES(194, '2022-02-03', 60);
INSERT INTO orders (product_id, action_date, quantity) VALUES(163, '2022-02-25', 12);
INSERT INTO orders (product_id, action_date, quantity) VALUES(194, '2022-02-25', 5);
INSERT INTO orders (product_id, action_date, quantity) VALUES(163, '2022-02-23', 18);
INSERT INTO orders (product_id, action_date, quantity) VALUES(125, '2022-02-25', 1);

Our SQL Query which resulting you needed:

select 
    prd.product_name, 
    today_orders.quantity as "Today Total",
    week_orders.quantity as "Last Week Total"
from 
    (
        select  product_id, sum(quantity) as quantity
        from orders
        where action_date = now()::date 
        group by product_id     
    ) today_orders
inner join 
    (
        select  product_id, sum(quantity) as quantity
        from orders
        where action_date >= (now()::date - 7) and action_date < now()::date 
        group by product_id
    ) week_orders on today_orders.product_id = week_orders.product_id 
inner join 
    products prd on today_orders.product_id = prd.id 

Result of our query:

CodePudding user response：

i did it like this with joinning SQL query's

Select a.restaurant, a.nb_milkshake_J, b.nb_milkshake_J_7, 
 (((a.nb_milkshake_J - b.nb_milkshake_J_7)/b.nb_milkshake_J_7)*100)
from 
(select restaurant,cast(sum(quantite) as float) as nb_milkshake_J   
from public.ventes_restaurant
where produit_groupe like 'MILKSHAKES'
and produit like 'MILKSHAKES%'
and date_commande ='2021/11/26' 
group by restaurant,date_commande 
) a full join 
(select restaurant,cast(sum(quantite) as float) as nb_milkshake_J_7   
from public.ventes_restaurant
where produit_groupe like 'MILKSHAKES'
and produit like 'MILKSHAKES%'
and date_commande ='2021/11/19' 
group by restaurant,date_commande) b  on a.restaurant = b.restaurant