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
