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
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 |