CODEWITHSUNDEEP
sql
Turin
Turin

Reputation: 127

GROUP BY Time to a 24 hour day

Given the following declarations where there is a registration of an arbitrary event for a user (USER) at a given time (TIME) in a table DATA: 

CREATE TABLE DATA
(
  "USER" Varchar(20),
  "TIME" Time
);


INSERT INTO DATA ("USER", "TIME") VALUES ('Martin', '14:58:00.000');
INSERT INTO DATA ("USER", "TIME") VALUES ('Martin', '15:02:11.000');
INSERT INTO DATA ("USER", "TIME") VALUES ('Martin', '15:48:44.000');
INSERT INTO DATA ("USER", "TIME") VALUES ('Marion', '08:45:01.000');
INSERT INTO DATA ("USER", "TIME") VALUES ('Marion', '15:01:01.000');
INSERT INTO DATA ("USER", "TIME") VALUES ('Marion', '15:03:48.000');

It's trivial to find the number of events per user at a given one-hour period: 

select 
    "USER", 
    extract(hour from "TIME") as "Hour",
    count(*)
from 
    DATA
group by
    "USER", 
    extract(hour from "TIME")
;

The result is, of course: 

USER  |Hour|COUNT
------+----+-----
Marion|   8|    1
Marion|  15|    2
Martin|  14|    1
Martin|  15|    2

But how do I get the number of events per user across an entire day in one hour intervals? Like this: 

USER  |Hour|COUNT
------+----+-----
Marion|   0|    0
Marion|   1|    0
...
Marion|   7|    0
Marion|   8|    1
Marion|   9|    0
...
Marion|  14|    0
Marion|  15|    2
Marion|  16|    0
...
Marion|  22|    0
Marion|  23|    0
Martin|   0|    0
Martin|   1|    0
...
Martin|  13|    0
Martin|  14|    1
Martin|  15|    2
Martin|  16|    0
...
Martin|  22|    0
Martin|  23|    0

BTW I won't have write access to any database involved.

Upvotes: 3

Views: 2378

Answers (2)

dfb
dfb

Reputation: 13289

You would want to do something like this, create a (temp) table HOURS with the numbers 0 to 23, then do an outer join to get all the HOUR values regardless of whether they are in DATA

select 
    "USER", 
    extract(hour from "TIME") as "Hour",
    SUM(CASE WHEN Data.HOUR is NOT NULL 1 ELSE 0 END)
from 
    DATA
    right outer join HOURS on extract(hour from "TIME") = HOURS.hour
group by
    "USER", 
    extract(hour from "TIME")
;

If you can't create a table, you can do ugly things like

(SELECT 1 as hour UNION 2 ... UNION 23) as HOURS

though there might be better way to do this depending your dialect

Upvotes: 5

Chains
Chains

Reputation: 13167

@spinning_plate's answer is good for a vertical answer (both ways -- temp table, or unions)

You might also like a horizontal result -- i.e. with headers: user | 0000 | 0100 | 0200 | ... | 2200 | 2300

If so, then some options...

with SQL-Server 2005+ or Oracle 11g+, look into PIVOT / UNPIVOT

with Oracle <11g, look into DECODE (e.g.: 

SELECT
   ...
   SUM(DECODE(extract(hour from "TIME"),1,1,0)) as 0100,
   SUM(DECODE(extract(hour from "TIME"),13,1,0)) as 1300,
...

Upvotes: 0

Related Questions