Hey stackoverflow community,
I currently do need a hint for a MYSQL query.
In my database are counters are stored for different “tags”.
Currently I already get the MIN and MAX from the current month. My challenge is, that I would need the last MAX from the previous month.
Anybody has an idea?
SELECT
CONCAT(YEAR(timestamp),"-",LPAD(MONTH(timestamp), 2, '0')) as DATE,
MIN(counter) as counterMIN,
MAX(counter) as counterMAX,
MIN(timestamp) as timestampMIN,
MAX(timestamp) as timestampMAX,
tag
FROM `seller`
WHERE tag LIKE "SYS%"
GROUP BY CONCAT(YEAR(timestamp),"-",MONTH(timestamp)),`tag`
ORDER BY CONCAT(YEAR(timestamp),"-",LPAD(MONTH(timestamp), 2, '0'))
| DATE | tag | counterMIN | counterMAX |
|---|---|---|---|
| 2023-12 | SYS001 | 46 | 312 |
| 2024-01 | SYS001 | 476 | 807 |
My challenge is that I would need the counterMAX value to be the value from the last month. Therefor I wanted to use a subquery but looks like I cannot do an subquery with an calculated field.
| DATE | tag | counterMIN | counterMAX | counterMAXPrevMonth |
|---|---|---|---|---|
| 2023-12 | SYS001 | 46 | 312 | 0 |
| 2024-01 | SYS001 | 476 | 807 | 312 |
MYSQL Data
CREATE TABLE `seller` (
`counter` decimal(10,0) NOT NULL,
`tag` varchar(6) NOT NULL,
`timestamp` timestamp NOT NULL
);
INSERT INTO `seller` (`counter`, `tag`, `timestamp`) VALUES
(46, 'SYS001', '2023-12-10 08:00:00'),
(199, 'SYS001', '2023-12-13 08:00:00'),
(312, 'SYS001', '2023-12-24 08:00:00'),
(476, 'SYS001', '2024-01-08 08:00:00'),
(493, 'SYS001', '2024-01-12 08:00:00'),
(618, 'SYS001', '2024-01-18 08:00:00'),
(807, 'SYS001', '2024-01-28 08:00:00'),
(858, 'SYS001', '2024-02-06 08:00:00'),
(961, 'SYS001', '2024-02-11 08:00:00'),
(1152, 'SYS001', '2024-02-26 08:00:00'),
(1281, 'SYS001', '2024-03-03 08:00:00'),
(1348, 'SYS001', '2024-03-07 08:00:00'),
(1397, 'SYS001', '2024-03-21 08:00:00'),
(1556, 'SYS001', '2024-04-06 07:00:00'),
(1625, 'SYS001', '2024-04-17 07:00:00'),
(172, 'SYS002', '2023-12-10 08:00:00'),
(334, 'SYS002', '2023-12-21 08:00:00'),
(519, 'SYS002', '2023-12-27 08:00:00'),
(630, 'SYS002', '2024-01-14 08:00:00'),
(790, 'SYS002', '2024-01-17 08:00:00'),
(884, 'SYS002', '2024-01-30 08:00:00'),
(928, 'SYS002', '2024-02-12 08:00:00'),
(938, 'SYS002', '2024-02-15 08:00:00'),
(1103, 'SYS002', '2024-02-23 08:00:00'),
(1289, 'SYS002', '2024-02-25 08:00:00'),
(1361, 'SYS002', '2024-02-27 08:00:00'),
(1553, 'SYS002', '2024-03-07 08:00:00'),
(1597, 'SYS002', '2024-03-15 08:00:00'),
(1604, 'SYS002', '2024-03-22 08:00:00'),
(1762, 'SYS002', '2024-03-29 08:00:00');
Hint for the query to resolve also MAX value from the previous month
New contributor
Znarf is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
WITH cte AS (
SELECT DATE_FORMAT(`timestamp`, '%Y-%m') as `DATE`,
MIN(counter) as counterMIN,
MAX(counter) as counterMAX,
MIN(`timestamp`) as timestampMIN,
MAX(`timestamp`) as timestampMAX,
tag
FROM `seller`
WHERE tag LIKE "SYS%"
GROUP BY `DATE`,`tag`
-- ORDER BY `DATE`
)
SELECT *, LAG(counterMAX) OVER (PARTITION BY tag ORDER BY `DATE`) counterMAXPrevMonth
FROM cte
ORDER BY tag, `DATE`
| DATE | counterMIN | counterMAX | timestampMIN | timestampMAX | tag | counterMAXPrevMonth |
|---|---|---|---|---|---|---|
| 2023-12 | 46 | 312 | 2023-12-10 08:00:00 | 2023-12-24 08:00:00 | SYS001 | null |
| 2024-01 | 476 | 807 | 2024-01-08 08:00:00 | 2024-01-28 08:00:00 | SYS001 | 312 |
| 2024-02 | 858 | 1152 | 2024-02-06 08:00:00 | 2024-02-26 08:00:00 | SYS001 | 807 |
| 2024-03 | 1281 | 1397 | 2024-03-03 08:00:00 | 2024-03-21 08:00:00 | SYS001 | 1152 |
| 2024-04 | 1556 | 1625 | 2024-04-06 07:00:00 | 2024-04-17 07:00:00 | SYS001 | 1397 |
| 2023-12 | 172 | 519 | 2023-12-10 08:00:00 | 2023-12-27 08:00:00 | SYS002 | null |
| 2024-01 | 630 | 884 | 2024-01-14 08:00:00 | 2024-01-30 08:00:00 | SYS002 | 519 |
| 2024-02 | 928 | 1361 | 2024-02-12 08:00:00 | 2024-02-27 08:00:00 | SYS002 | 884 |
| 2024-03 | 1553 | 1762 | 2024-03-07 08:00:00 | 2024-03-29 08:00:00 | SYS002 | 1361 |
fiddle