Message-ID: <4CEF9C66.30100@unipex.it>
Date: Fri, 26 Nov 2010 12:39:18 +0100
From: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.8.1b1) Gecko/20060724 Thunderbird/2.0a1 Mnenhy/0.7.4.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: group by hour + distinct
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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