From: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
To: pgsql-sql@postgresql.org
Subject: group by hour + distinct
Date: Fri, 26 Nov 2010 12:39:18 +0100
Message-ID: <4CEF9C66.30100@unipex.it> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: michele.petrazzo@unipex.it
Subject: Re: group by hour + distinct
In-Reply-To: <4CEF9C66.30100@unipex.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox