Re: group by hour + distinct

Howdy, Michelle,

If you write something like this,

SELECT hour , COUNT(id_user) as count
FROM
(
SELECT EXTRACT(hour from my_date) as hour, id_user FROM michelle
GROUP BY EXTRACT(hour from my_date),id_user
)subquery
GROUP BY hour

for each hour it will count the number of distinct user_id's there are . If
I understood correctly what you need...

Can you please test it and see if it is OK for your needs?
With me, it worked on the sample data you provided

Best,
Oliver

----- Original Message -----
From: "Michele Petrazzo - Unipex" <michele(dot)petrazzo(at)unipex(dot)it>
To: <pgsql-sql(at)postgresql(dot)org>
Sent: Friday, November 26, 2010 11:39 AM
Subject: [SQL] group by hour + distinct

> 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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql