How to sum non-consecutive values when using group by in MySQL

user_15754364 0
Viewed 89

I have a data set representing alarms' state at a given timestamp (every 15 minutes). When the value is 1 the alarm is ON, 0 when OFF. I am trying to count the number of times the alarm has been triggered per hour (non-consecutive 1).

I took a look at Count max number of consecutive occurrences of a value in SQL Server but couldn't manage to adapt the answer.

Basically the data set for one alarm looks like this:

| id |   value  |    registered_at    |   
| -- | ---------|---------------------|
|  1 |     1    | 2012-07-15  06:00   |
|  2 |     0    | 2012-07-15  06:15   |
|  3 |     1    | 2012-07-15  06:30   |
|  4 |     0    | 2012-07-15  06:45   |
|  5 |     1    | 2012-07-15  07:00   |
|  6 |     1    | 2012-07-15  07:15   |
|  7 |     1    | 2012-07-15  07:30   |
|  8 |     0    | 2012-07-15  07:45   |
|  8 |     0    | 2012-07-15  08:00   |

The results I am looking for is the following

|   registered_at    | alarm_triggered | 
|--------------------|-----------------|
|   2012-07-15  06   |        2        |
|   2012-07-15  07   |        1        |
|   2012-07-15  08   |        0        |

To create groups I use EXTRACT(DAY_HOUR from registered_at). Can you help me create the query?

(First time poster on SO, any feedback about the form of this post would be greatly appreciated as well)

subquery group-by mysql
4 Answers

Use LAG() window function to check the value of value of the previous row and if it is different and the current row is 1 then sum:

SELECT registered_at, 
       SUM(value * flag) alarm_triggered 
FROM (
  SELECT value,
         DATE_FORMAT(registered_at, '%Y-%m-%d %H') registered_at, 
         value <> LAG(value, 1, 0) OVER (PARTITION BY DATE_FORMAT(registered_at, '%m-%d-%Y %H') ORDER BY registered_at) flag
  FROM tablename
) t
GROUP BY registered_at

See the demo.
Results:

registered_at alarm_triggered
2012-07-15 06 2
2012-07-15 07 1
2012-07-15 08 0
user_104988280

I assume the registered_at field is datetime so you need to use datetime function.

here is a query for this:

SELECT DATE_FORMAT(registered_at, "%Y-%m-%d %H:00:00") AS registered_at, SUM(VALUE) AS alarm_triggered 
FROM ALARMS
GROUP BY DATE_FORMAT(registered_at, "%Y-%m-%d %H:00:00")

and sqlfiddle to see example:

example

user_22187130

If you need only notified days

select count(value), date_format(registered_at, '%m-%d-%Y %H') as c_at
from notifications
where value = 1
group by date_format(registered_at, '%m-%d-%Y %H');

Or all days

select sum(value), date_format(registered_at, '%m-%d-%Y %H') as c_at
from notifications
group by date_format(registered_at, '%m-%d-%Y %H');

Try this!

user_83235340

You can select it like this:

SELECT CONCAT(YEAR(registered_at), '-', MONTH(registered_at), '-', DAYOFMONTH(registered_at), ' ' HOUR(registered_at)), count(*)
FROM alarms
WHERE value = 1
GROUP BY YEAR(registered_at), MONT(registered_at), DAYOFMONTH(registered_at), HOUR(registered_at);

Explanation

First, we find the records whose value is 1, then group them by year, month, day of month and hour and finally we find out their count.

user_4365600
Related