0

I have a Table similar to:

CREATE TABLE `stats` (
    `stat_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `stat_Start` timestamp NULL DEFAULT NULL,
    `stat_values` smallint(5) unsigned DEFAULT NULL,
    PRIMARY KEY (`stat_ID`),
    );

It contains a list of events with the time it happenned, and the value.

I am now looking to generate a report from this similar to:

SELECT
    LEFT(stat_start,16) AS `time`,
    AVG(`stat_values`) AS `data` 
FROM stats s 
WHERE s.stat_Start BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW()
GROUP BY LEFT(s.stat_start,16) 
ORDER BY stat_start;

In other words, get the Average value for all entries per minute for the past hour.

However, in some cases I don't have an entry for some minutes, but I still need to know that there were 0 results for that minute. Is there a way of achieving this in MySQL?

so I might get something like:

'2017-04-07 09:01', 102,
'2017-04-07 09:02', 0, --> row for which no events were recorded
'2017-04-07 09:03', 132
1

1 Answer 1

0

Filling in 'missing' rows is done via an extra table with all possible values, plus a LEFT JOIN:

Create have a table all_minutes with all possible minutes (in some finite range). (If using MariaDB, use a "Sequence" table.) Then

SELECT a.time,
       IFNULL(s.data, 0)  AS `data`  -- assuming you want 0, not NULL
    FROM all_minutes AS a
    LEFT JOIN ( SELECT
            LEFT(stat_start,16) AS `time`,  -- eg, '2017-04-07 11:03'
            AVG(`stat_values`) AS `data` 
        FROM stats
        WHERE stat_Start BETWEEN NOW() - INTERVAL 1 HOUR AND NOW()
        GROUP BY `time`
        ORDER BY `time`    -- note a change here
              ) AS s    ON s.time = a.time
    WHERE a.time BETWEEN ... AND ...; -- whatever range you need

Note: Both tables should exclude or include the :00 for seconds. Otherwise (I think) there could be some difficulty in doing the = in the ON.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.