CODEWITHSUNDEEP
mysqlsql
João Eduardo Bertotti
João Eduardo Bertotti

Reputation: 63

MySql query group by day and by time

I'm trying create an SQL query to resolve my problem.

My Table:

+----+---------------------+-------+
| id |         date        | value |
+----+---------------------+-------+
| 1  | 2014-10-10 05:10:10 |  10   |
+----+---------------------+-------+
| 2  | 2014-10-10 09:10:10 |  20   |
+----+---------------------+-------+
| 3  | 2014-10-10 15:10:10 |  30   |
+----+---------------------+-------+
| 4  | 2014-10-10 23:10:10 |  40   |
+----+---------------------+-------+
| 5  | 2014-10-11 08:10:10 |  15   |
+----+---------------------+-------+
| 6  | 2014-10-11 09:10:10 |  25   |
+----+---------------------+-------+
| 7  | 2014-10-11 10:10:10 |  30   |
+----+---------------------+-------+
| 8  | 2014-10-11 23:10:10 |  40   |
+----+---------------------+-------+

I want to sum value in groups by days and this days in three sub groups like a 'morning'(06:00 - 12:00), 'afternoon'(12:00 - 18:00) and 'night'(00:00 - 06:00 and 18:00 - 24:00).
something like this:

+------------+-------+---------+-----------+-------+
|    date    | value | morning | afternoon | night |
+------------+-------+---------+-----------+-------+
| 2014-10-10 |  100  |   20    |     30    |  50   |
+------------+-------+---------+-----------+-------+
| 2014-10-11 |  110  |   70    |     0     |  40   |
+------------+-------+---------+-----------+-------+

Upvotes: 2

Views: 988

Answers (2)

mrmillsy
mrmillsy

Reputation: 495

There are multiple ways to go about this, but for myself I'd do it by first extracting the pseudo information in a CROSS APPLY, and then grouping on this information.

I believe this offers significant readibility benefits, and allows you to re-use any calculations in other clauses. For example, you have centralised the grouping mechanism, meaning that you only need to change it in the one place rather than in the select and the group by. Similarly, you could add "extraData.Morning = 1" to a WHERE clause rather than re-writing the calculation for mornings.

For example:

CREATE TABLE #TestData (ID INT, Data DATETIME, Value INT)

INSERT INTO #TestData (ID, Data, Value) VALUES
    (1  ,'2014-10-10 05:10:10'   ,10)
    ,(2   ,'2014-10-10 09:10:10'   ,20)
    ,(3   ,'2014-10-10 15:10:10'   ,30)
    ,(4   ,'2014-10-10 23:10:10'   ,40)
    ,(5   ,'2014-10-11 08:10:10'   ,15)
    ,(6   ,'2014-10-11 09:10:10'   ,25)
    ,(7   ,'2014-10-11 10:10:10'   ,30)
    ,(8   ,'2014-10-11 23:10:10'   ,40)

SELECT
    extraData.DayComponent
    ,SUM(td.Value)
    ,SUM(CASE WHEN extraData.Morning = 1 THEN td.Value ELSE 0 END) AS Morning
    ,SUM(CASE WHEN extraData.Afternoon = 1 THEN td.Value ELSE 0 END) AS Afternoon
    ,SUM(CASE WHEN extraData.Night = 1 THEN td.Value ELSE 0 END) AS Night
FROM #TestData td
    CROSS APPLY (
        SELECT
            DATEADD(dd, 0, DATEDIFF(dd, 0, td.Data))        AS DayComponent
            ,CASE WHEN DATEPART(HOUR, td.Data) BETWEEN 6 AND 12 THEN 1 ELSE 0 END AS Morning
            ,CASE WHEN DATEPART(HOUR, td.Data) BETWEEN 12 AND 18 THEN 1 ELSE 0 END AS Afternoon
            ,CASE WHEN DATEPART(HOUR, td.Data) BETWEEN 0 AND 6 
                OR DATEPART(HOUR, td.Data) BETWEEN 18 AND 24 THEN 1 ELSE 0 END AS Night
    ) extraData
GROUP BY
    extraData.DayComponent

DROP TABLE #TestData

Upvotes: 0

Mureinik
Mureinik

Reputation: 310993

You could use a couple of sums over case expressions:

SELECT   DAY(`date`) AS `date`
         SUM(CASE WHEN HOUR(`date`) BETWEEN 6 AND 12 THEN value ELSE 0 END) AS `morning`,
         SUM(CASE WHEN HOUR(`date`) BETWEEN 12 AND 18 THEN value ELSE 0 END) AS `afternoon`,
         SUM(CASE WHEN HOUR(`date`) < 6 OR HOUR(`date`) > 18 THEN value ELSE 0 END) AS `evening`
FROM     my_table
GROUP BY DAY(`date`)

Upvotes: 1

Related Questions