How to select count of each user by time period (e.g., hour)?

Question

How do I write a Postgresql query to find the counts for users by hour?

Table:

date                 name
-------------------  ----
2015-01-01 23:11:11  John
2015-02-02 23:22:22  John
2015-02-02 23:00:00  Mary
2015-02-02 23:59:59  Mary
2015-03-03 00:33:33  Mary

Desired output:

        hour         | name    | count
---------------------+---------+-------
 2015-01-01 23:00:00 | John    |     1
 2015-02-02 23:00:00 | Mary    |     2
 2015-02-02 23:00:00 | John    |     1
 2015-03-03 00:00:00 | Mary    |     1

I tried this http://www.sqlfiddle.com/#!12/a50d4/2:

CREATE TABLE my_table (
    date TIMESTAMP WITHOUT TIME ZONE,
    name TEXT
);
INSERT INTO my_table (date, name) VALUES ('2015-01-01 23:11:11', 'John');
INSERT INTO my_table (date, name) VALUES ('2015-02-02 23:22:22', 'John');
INSERT INTO my_table (date, name) VALUES ('2015-02-02 23:00:00', 'Mary');
INSERT INTO my_table (date, name) VALUES ('2015-02-02 23:59:59', 'Mary');
INSERT INTO my_table (date, name) VALUES ('2015-03-03 00:33:33', 'Mary');

SELECT DISTINCT
       date_trunc('hour', "date") AS hour,
       name,
       count(*) OVER (PARTITION BY date_trunc('hour', "date")) AS count
FROM my_table
ORDER BY hour, count;

but it gives me:

 hour                | name | count |
---------------------|------|-------|
 2015-01-01 23:00:00 | John |     1 |
 2015-02-02 23:00:00 | Mary |     3 |
 2015-02-02 23:00:00 | John |     3 |
 2015-03-03 00:00:00 | Mary |     1 |

Similar:

You only need a window function for a *running* count (then you also need `ORDER BY` in the `OVER` clause to make it meaningful). For a plain count per hour, a plain aggregate function does the job. — Erwin Brandstetter, Apr 03 '15 at 23:35
Please do not edit my answer as you did twice and I rolled back both. — Clodoaldo Neto, Apr 25 '15 at 19:50

score 4 · Accepted Answer · edited Apr 04 '15 at 14:45

4

If you want to stick with the window functions, you need to add "name" into your list like this:

http://www.sqlfiddle.com/#!12/a50d4/51

SELECT DISTINCT
   date_trunc('hour', "date") AS "hour",
   name,
   count(name) OVER (PARTITION BY name, date_trunc('hour', "date")) AS "cnt"
FROM my_table
ORDER BY hour, cnt DESC

edited Apr 04 '15 at 14:45

Rob Bednark

25,981
23
80
125

answered Apr 03 '15 at 20:38

Bob

1,045
8
10

hahaha you edited your answer exactly to me except column name :D – Khurram Ali Apr 03 '15 at 20:53
I didn't notice that. I've removed that part since it was duplicated. – Bob Apr 03 '15 at 20:55

Clodoaldo Neto · Answer 2 · 2015-04-25T19:46:05.007

3

select
    date_trunc('hour', "date") as "hour",
    name,
    count(*) as "count"
from my_table
group by 1, 2
order by 1, 3 desc;

http://www.sqlfiddle.com/#!12/a50d4/62

edited Apr 25 '15 at 19:46

answered Apr 03 '15 at 20:32

Clodoaldo Neto

118,695
26
233
260

Aaron Sykes · Answer 3 · 2015-04-03T21:21:12.660

1

This worked for me: http://www.sqlfiddle.com/#!12/a50d4/57

SELECT DISTINCT name,
                date_trunc('hour', date) as hour,
                count(*) as count
FROM my_table
GROUP BY name, hour
ORDER BY hour, count DESC;

edited Apr 03 '15 at 21:21

answered Apr 03 '15 at 20:59

Aaron Sykes

11
3

Khurram Ali · Answer 4 · 2015-04-03T20:49:41.823

I think you are looking for this

SELECT DISTINCT
       date_trunc('hour', "date") AS hour,
       name,
       count(*) as tcount
FROM my_table
GROUP BY  hour,name
ORDER BY  hour, count(*) Desc

Output:

        hour         | name    | count
---------------------+---------+-------
 2015-01-01 23:00:00 | John    |     1
 2015-02-02 23:00:00 | Mary    |     2
 2015-02-02 23:00:00 | John    |     1
 2015-03-03 00:00:00 | Mary    |     1

SQLFIDDLE

How to select count of each user by time period (e.g., hour)?

4 Answers4