How can I group table data?

Question

I have a following table:

mysql> select * from playground order by locatedAt;
+------------+---------------------+--------------+
| macAddress | locatedAt           | status       |
+------------+---------------------+--------------+
| device1    | 2014-08-11 01:20:27 | connected    |
| device2    | 2014-08-11 01:20:27 | connected    |
| device2    | 2014-08-11 01:30:27 | disconnected |
| device1    | 2014-08-11 01:30:27 | connected    |
| device2    | 2014-08-11 01:40:27 | disconnected |
| device1    | 2014-08-11 01:40:27 | disconnected |
| device2    | 2014-08-11 01:49:27 | connected    |
| device1    | 2014-08-11 01:49:27 | connected    |
| device1    | 2014-08-11 01:50:27 | disconnected |
| device2    | 2014-08-11 01:53:27 | disconnected |
| device1    | 2014-08-11 01:55:27 | disconnected |
| device1    | 2014-08-11 02:05:27 | disconnected |
| device2    | 2014-08-11 02:10:27 | disconnected |
| device2    | 2014-08-11 02:15:27 | disconnected |

I need the data to be summarized in such a way that it should be grouped by hour of the day and by the status of the device. Expected output:

hour | connected | disconnected
01   |  5        | 6
02   |  0        | 3

How can I do this?

Answer 1

Do just that. Get the hour of the locatedAt column with the function HOUR sum up the status values and group by the DATE part and the HOUR part of the locatedAt column:

SELECT
    HOUR(locatedAt) hour,  
    SUM(CASE WHEN status='connected' THEN 1 ELSE 0 END) connected,
    SUM(CASE WHEN status='disconnected' THEN 1 ELSE 0 END) disconnected
FROM
    playground
GROUP BY 
    DATE(locatedAt), HOUR(locatedAt);

You can simplify the expression as Strawberry mentioned:

SELECT
    HOUR(locatedAt) hour,  
    SUM(status='connected') connected,
    SUM(status='disconnected') disconnected
FROM
    playground
GROUP BY 
    DATE(locatedAt), HOUR(locatedAt);

because in MySQL TRUE is implemented as 1 and FALSE as 0, see Logical Operators:

In SQL, all logical operators evaluate to TRUE, FALSE, or NULL (UNKNOWN). In MySQL, these are implemented as 1 (TRUE), 0 (FALSE), and NULL.

Working in a fiddle