group by hour + distinct

From: Michele Petrazzo - Unipex <michele(dot)petrazzo(at)unipex(dot)it>
To: pgsql-sql(at)postgresql(dot)org
Subject: group by hour + distinct
Date: 2010-11-26 11:39:18
Message-ID: 4CEF9C66.30100@unipex.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi list,
Into a table like this:
id_user | my_date

and some data inside
1 | 2010/11/25 00:01:00
1 | 2010/11/25 00:02:00
1 | 2010/11/25 01:01:00
2 | 2010/11/25 02:01:00
3 | 2010/11/25 02:01:00
3 | 2010/11/25 02:06:00
1 | 2010/11/25 03:01:00

I'm looking for a query that say me, hour per hour, how many unique
id_user are inside that range.
With the simple data above, I'm looking for:
hour | count
0 | 1
1 | 1
2 | 2
3 | 1

Like now, with my tests, I achieve only a
hour | count
0 | 2
1 | 1
2 | 3
3 | 1

My real query and data:

SELECT count(id_user) from some_table where my_date >= '2010/11/25
00:00:00' and my_date < '2010/11/25 01:00:00';
count
-------
90
(1 row)

SELECT distinct(id_user) from some_table where my_date >= '2010/11/25
00:00:00' and my_date < '2010/11/25 01:00:00' order by id_user;

Give me 69 rows, that are the real unique id_user that I have and I'm
looking for.

One of a query that I use without success:

SELECT count(distinct some_table.my_date), EXTRACT(hour from my_date) as
h from some_table where my_date >= '2010/11/25' and my_date <
'2010/11/26' group by h order by h;
count | h
-------+----
90 | 0
63 | 1
... and so on

Someone?
Thanks,
Michele

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Oliveiros d'Azevedo Cristina 2010-11-26 12:19:06 Re: group by hour + distinct
Previous Message Pavel Stehule 2010-11-26 07:45:28 Re: error null value in column" concat_id" violates not-null constraint