Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Sum values between a lot of date-ranges in a single column

I’m trying to figure out how to get a sum of value by transaction dates over a 90 day period and repeated over a single year’s worth of transaction dates.

This is what I have

transaction_date value
01-01-2024 20
02-01-2024 50
03-01-2024 30

With the intended outcome of:

transaction_date date_diff value
01-01-2024 03-10-2023 (20+ all transaction date value between 03-10-2023 and 01-01-2023
02-01-2024 04-10-2023 20 + 50 + predated value
03-01-2024 05-10-2023 20 + 50 + 30 + predated value

For date_diff, I got the date via:

(transaction_date) – 90 DAYs as date_diff90

Tags:

2

Answers


  1. 0

    Try to using a self join and Interval function
    I did n’t test yet but it should be something like that

    SELECT
    t1.transaction_date,
    DATE_SUB(t1.transaction_date, INTERVAL 90 DAY) AS date_diff,
    SUM(t2.value) AS value
From
    transactions t1
join
    transactions t2
ON
    t2.transaction_date BETWEEN DATE_SUB(t1.transaction_date, INTERVAL 90 DAY) AND t1.transaction_date
GROUP BY
    t1.transaction_date
ORDER BY
    t1.transaction_date;

    Using Interval 90 day to calculate previous 90 days

    Login or Signup to reply.
  2. 0

    A simple correlated subquery will do.

    create table test(transaction_date date,value int);
insert test values
('2024-01-01',  20),
('2024-01-02',  80),
('2024-01-03',  30),
('2024-02-01',  10),
('2024-02-02',  50),
('2024-03-03',  40),
('2024-03-05',  20),
('2024-04-02',  150),
('2024-04-03',  30),
('2024-05-04',  20),
('2024-05-05',  250),
('2024-06-03',  30),
('2024-06-04',  20),
('2024-07-02',  250),
('2024-07-10',  130)
;
select * from test;
--result set:
+------------------+-------+
| transaction_date | value |
+------------------+-------+
| 2024-01-01       |    20 |
| 2024-01-02       |    80 |
| 2024-01-03       |    30 |
| 2024-02-01       |    10 |
| 2024-02-02       |    50 |
| 2024-03-03       |    40 |
| 2024-03-05       |    20 |
| 2024-04-02       |   150 |
| 2024-04-03       |    30 |
| 2024-05-04       |    20 |
| 2024-05-05       |   250 |
| 2024-06-03       |    30 |
| 2024-06-04       |    20 |
| 2024-07-02       |   250 |
| 2024-07-10       |   130 |
+------------------+-------+

    Here is the query:

    select transaction_date,
date_sub(transaction_date,interval +90 day) as date_diff,
(select sum(value) from test where transaction_date between date_sub(t.transaction_date,interval +90 day) and t.transaction_date ) as value
from test t;
-- result set:
+------------------+------------+-------+
| transaction_date | date_diff  | value |
+------------------+------------+-------+
| 2024-01-01       | 2023-10-03 |    20 |
| 2024-01-02       | 2023-10-04 |   100 |
| 2024-01-03       | 2023-10-05 |   130 |
| 2024-02-01       | 2023-11-03 |   140 |
| 2024-02-02       | 2023-11-04 |   190 |
| 2024-03-03       | 2023-12-04 |   230 |
| 2024-03-05       | 2023-12-06 |   250 |
| 2024-04-02       | 2024-01-03 |   300 |
| 2024-04-03       | 2024-01-04 |   300 |
| 2024-05-04       | 2024-02-04 |   260 |
| 2024-05-05       | 2024-02-05 |   510 |
| 2024-06-03       | 2024-03-05 |   500 |
| 2024-06-04       | 2024-03-06 |   500 |
| 2024-07-02       | 2024-04-03 |   600 |
| 2024-07-10       | 2024-04-11 |   700 |
+------------------+------------+-------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search