CODEWITHSUNDEEP
sqlite
Durin
Durin

Reputation: 687

Avoid repetition of subquery

I have a table messages that contains a column message_internaldate. Now I'd like to count the messages within certain time periods (each hour of a day) over several months. I could manage to get the sum of messages per hour by having lots of subqueries (24) but I hope that there is a more brainy way to do that. The subqueries are similar except that the time period changes. Any suggestions?

e.g. for the first two hours 

SELECT T1, T2 FROM 
(
SELECT sum(T1c) as T1 FROM
    (
    SELECT strftime('%H:%M',message_internaldate) AS T1s ,count(*) as T1c FROM messages WHERE
    message_internaldate BETWEEN '2005-01-01 00:00:00' AND '2012-12-31 00:00:00'
    AND strftime('%H:%M',message_internaldate) BETWEEN '01:00'AND '01:59'
    GROUP BY strftime('%H:%M',message_internaldate)
    )
)
,
(
SELECT sum(T2c) as T2 FROM
    (
    SELECT strftime('%H:%M',message_internaldate) AS T2s ,count(*) as T2c FROM messages WHERE
    message_internaldate BETWEEN '2005-01-01 00:00:00' AND '2012-12-31 00:00:00'
    AND strftime('%H:%M',message_internaldate) BETWEEN '02:00'AND '02:59'
    GROUP BY strftime('%H:%M',message_internaldate)
    )
)
...

Upvotes: 0

Views: 63

Answers (1)

CL.
CL.

Reputation: 180091

Your problem is that you want to have the individual hours as columns.

To get them as rows, try a query like this:

SELECT strftime('%H', message_internaldate) AS hour,
       strftime('%H:%M', message_internaldate) AS Ts,
       COUNT(*) AS Tc
FROM messages
WHERE message_internaldate BETWEEN '2005-01-01 00:00:00' AND '2012-12-31 23:59:59'
GROUP BY 1, 2

Upvotes: 1

Related Questions