MYSQL PHP IF(SUM()) between time range

Question

I've created a query that selects the sum() of a period of 5 hours but am having an issue getting desired results. They way it's currently written I get repeated values 21.47,21.47,21.47,21.47,21.47 or [{"oneHour":"229376","twoHour":"0","threeHour":"0","fourHour":"0","fiveHour":"0"}] as response depending on how I get the results.

Any help would be greatly appreciated.

CREATE TABLE `numbers` (
  `id` int(id) NOT NULL,
  `inserttime` datetime NOT NULL DEFAULT current_timestamp(),
  `number` decimal(11,2) NOT NULL DEFAULT 0.00
)

SELECT CASE WHEN inserttime BETWEEN NOW() - INTERVAL 60 MINUTE AND NOW() THEN SUM(number) ELSE 0 END AS oneHour,
CASE WHEN inserttime BETWEEN NOW() - INTERVAL 120 MINUTE AND NOW() - INTERVAL 60 MINUTE THEN SUM(number) ELSE 0 END AS twoHour,
CASE WHEN inserttime BETWEEN NOW() - INTERVAL 180 MINUTE AND NOW() - INTERVAL 120 MINUTE THEN SUM(number) ELSE 0 END AS threeHour,
CASE WHEN inserttime BETWEEN NOW() - INTERVAL 240 MINUTE AND NOW() - INTERVAL 180 MINUTE THEN SUM(number) ELSE 0 END AS fourHour,
CASE WHEN inserttime BETWEEN NOW() - INTERVAL 300 MINUTE AND NOW() - INTERVAL 240 MINUTE THEN SUM(number) ELSE 0 END AS fiveHour
FROM `numbers` WHERE inserttime BETWEEN NOW() - INTERVAL 300 MINUTE AND NOW()

To clarify there is valid data in the database over that 5 hour period that would result in different values in each element . — unknown1, May 06 '21 at 21:46
Please don't just ask the same question multiple times. This is identical to your previous question. — MatBailie, May 06 '21 at 22:25
It makes no sense to put `SUM` inside `WHEN`. `WHEN` is testing a single row, but `SUM()` operates on a group of rows. — Barmar, May 06 '21 at 23:15
@barmar I hate that MySQL 5.x even allows these queries to run. — MatBailie, May 07 '21 at 05:47

MYSQL PHP IF(SUM()) between time range

0 Answers0