CODEWITHSUNDEEP
mysql
Raed N.
Raed N.

Reputation: 172

query to calculate sum of sales based on a historical related records

I have a sample sales table that contains multiple stores' daily sales figures. I am trying to get a same-store sales query. What that means, I want to sum the total daily sales of the stores that existed a year ago from the date range I have.

Here is the sample table and data:

CREATE TABLE `sales` (
  `store_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `total_sales` double NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `sales` (`store_id`, `date`, `total_sales`) VALUES
(1, '2022-03-01', 100),
(1, '2022-03-02', 100),
(1, '2022-03-03', 100),
(1, '2021-03-01', 50),
(1, '2021-03-03', 50),
(2, '2022-03-01', 30),
(2, '2022-03-02', 30),
(2, '2022-03-03', 30),
(2, '2021-03-01', 15),
(2, '2021-03-02', 15),
(2, '2021-03-03', 15),
(3, '2022-03-01', 500),
(3, '2022-03-02', 500),
(3, '2022-03-03', 500);

In this case, I want to be able to select the sum of daily sales from 1 march 2022 until 2 march 2022 for all the stores that had sales between 1 march 2021 and 2 march 2021 grouped by date. The challenge is, if a store didn’t have sales on a specific day last year, the corresponding sales record of this year should not be added to the sum total. Based on the sample table above, I expect the output of the desired query to be:

SELECT SALES BETWEEN 01-03-2022 AND 02-03-2022

+-----------------------------+
| Date          |  Sales      |
+-----------------------------+
| 01-03-2022    | 130         |
| 02-03-2022    | 30          |
+-----------------------------+

So for 01-03-2022, we check which stores had sales in 01-03-2021, we get that stores 1 and 2 (not 3) so the total sales of store 1 and 2 for 01-03-2022 is: 100 + 30 = 130

For 02-03-2022, we check last year sales, we had only store 2 with sales on 02-03-2021, so the sum of sales for 2022 is the sales of store 2 on 02-03-2022 which is 30

How can I accomplish that with a MySQL query?

Upvotes: 0

Views: 116

Answers (1)

forpas
forpas

Reputation: 164099

First you need a calendar table that returns all the dates for the date range that you want, or you can create the dates with a recursive cte.

Then LEFT join to the cte 2 copies of the table, one for the previous year's dates and the other for the date range's dates and finally aggregate:

WITH RECURSIVE dates(date) AS (
  SELECT '2022-03-01' 
  UNION ALL
  SELECT date + INTERVAL 1 day
  FROM dates
  WHERE date < '2022-03-02' 
)
SELECT d.date,
       COALESCE(SUM(s2.total_sales), 0) total_sales
FROM dates d 
LEFT JOIN sales s1 ON s1.date = d.date - INTERVAL 1 year 
LEFT JOIN sales s2 ON s2.date = d.date AND s2.store_id = s1.store_id
GROUP BY d.date;

See the demo.

Upvotes: 1

Related Questions