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

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)

Solution

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