CODEWITHSUNDEEP
mysqlsqldatetimesumwindow-functions
Michi
Michi

Reputation: 5471

Calculate difference between multiple timestamps

DB-Fiddle:

CREATE TABLE sales (
    id int auto_increment primary key,
    time_stamp DATE,
    product VARCHAR(255),
    sales_quantity INT
);

INSERT INTO sales
(time_stamp, product, sales_quantity
)
VALUES 
("2020-01-14", "Product_A", "100"),
("2020-01-14", "Product_B", "300"),
("2020-01-14", "Product_C", "600"),

("2020-01-15", "Product_A", "100"),
("2020-01-15", "Product_B", "350"),
("2020-01-15", "Product_C", "600"),

("2020-01-16", "Product_A", "130"),
("2020-01-16", "Product_B", "350"),
("2020-01-16", "Product_C", "670"),
("2020-01-16", "Product_D", "400"),

("2020-01-17", "Product_A", "130"),
("2020-01-17", "Product_B", "350"),
("2020-01-17", "Product_C", "700"),
("2020-01-17", "Product_D", "450");

Expected Result:

time_stamp          difference
2020-01-14             0   
2020-01-15            50   = (100+350+600) - (100+300+600)
2020-01-16           500   = (130+350+670+400) - (100+350+600)
2020-01-17            80   = (130+350+700+450) - (130+350+670+400)

In the above result I want to calculate the difference of the sales_quantiy between multiple timestamps.

I tried to go with this query:

SELECT
time_stamp,
(SUM(t1.time_stamp_01) - SUM(t1.time_stamp_02)) AS difference
FROM
  (SELECT 
  time_stamp,
  SUM(CASE WHEN time_stamp  = '2020-01-14' THEN sales_quantity ELSE 0 END) AS time_stamp_01,
  SUM(CASE WHEN time_stamp  = '2020-01-15' THEN sales_quantity ELSE 0 END) AS time_stamp_02
  FROM sales
  GROUP BY 1) t1
GROUP BY 1;

However, it somehow does not calcualte the difference between the timestamps and it is also limited to two timestamps.

How do I need to modify the query to get the expected result?

Upvotes: 0

Views: 111

Answers (1)

GMB
GMB

Reputation: 222482

If you are running MySQL 8.0, you can use aggregation and lag():

select time_stamp, 
    sum(sales_quantity) 
        - lag(sum(sales_quantity), 1, sum(sales_quantity)) over(order by time_stamp) diff
from sales
group by time_stamp
order by time_stamp

This works in MySQL 8.0, but not in MariaDB 10.3 (which is what your fiddle uses). You can use:

select time_stamp, sum_sales_quantity - coalesce(lag(sum_sales_quantity) over(order by time_stamp), sum_sales_quantity) diff
from (
    select time_stamp, sum(sales_quantity) sum_sales_quantity
    from sales
    group by time_stamp
) t
order by time_stamp

Upvotes: 1

Related Questions