[SQL] group by hour + distinct

[Michele Petrazzo - Unipex]

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] group by hour + distinct

[Oliveiros d'Azevedo Cristina]

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" 

To: 
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql 



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] group by hour + distinct

[Michele Petrazzo - Unipex]

Oliveiros d'Azevedo Cristina ha scritto:

Howdy, Michelle,



Ciao


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



Yes! Simply perfect!


Best, Oliver



Thanks,
Michele

P.s. Have you some references about the "subquery" keyword? I found only
the word subquery as "use" (for example: select a from b where id in 
(select id from table)), but not as sql command.



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] group by hour + distinct

[Oliveiros d'Azevedo Cristina]

Ciao


* Olá!


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



Yes! Simply perfect!



* Great to hear it worked !





Thanks,
Michele


* Don't mention it ;-)



P.s. Have you some references about the "subquery" keyword? I found only
the word subquery as "use" (for example: select a from b where id in 
(select id from table)), but not as sql command.





* I don't know subquery as an SQL keyword too. In the query I gave you the 
"subquery" thing was just an alias. Such kind of things are mandatory. If I 
try to omit it, I get this error


ERROR:  subquery in FROM must have an alias
HINT:  For example, FROM (SELECT ...) [AS] foo.

** Erro **

ERROR: subquery in FROM must have an alias
Estado de SQL:42601
Sugestão:For example, FROM (SELECT ...) [AS] foo.

So, even though the "subquery" alias was not used anywhere else, I believe 
it had to be there, otherwise the SQL parser would complain



Best,
Oliver


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Insert row in 1.10.1 and 1.10.3

[ndias]

Hello to all,

I have a question regarding the behavior of insert row in the two versions,
tried to search for it but could not find a similar case, please excuse if
this has already been responded.

When doing a insert row with less columns mentioned in "into table(col1,
col2, col3,..." than the columns that exist on the table, on 1.10.1 it
returns an error saying "INSERT has more expressions than target columns"
(the error is translated so maybe the text is not exactly like this).
Although, when this is insert is done on our test environment, where the
version is 1.10.3 it works fine. The tables have the same columns and so on.
Do you think this is really a matter of the different versions or am I
missing something else?
If this is in fact due to the version, can I upgrade the version without any
problem and is there any guide to do so?

Have also confirmed that the values are as many as the columns specified on
the "into table(col1, col2, col3,..."...

Thank you very much.

Best regards,
Nuno
-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Insert-row-in-1-10-1-and-1-10-3-tp3281512p3281512.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] group by hour + distinct

[Lew]

Michele Petrazzo - Unipex wrote:

P.s. Have you some references about the "subquery" keyword? I found only
the word subquery as "use" (for example: select a from b where id in
(select id from table)), but not as sql [sic] command.


"subquery" is not an SQL keyword.


Nor is it a command all by itself.

Reading the documentation might help you:




Or try:


--
Lew

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Insert row in 1.10.1 and 1.10.3

[Oliveiros d'Azevedo Cristina]

Could you please
include the full insert SQL statement?

Best,
Oliveiros

- Original Message - 
From: "ndias" 

To: 
Sent: Friday, November 26, 2010 4:22 PM
Subject: [SQL] Insert row in 1.10.1 and 1.10.3




Hello to all,

I have a question regarding the behavior of insert row in the two 
versions,

tried to search for it but could not find a similar case, please excuse if
this has already been responded.

When doing a insert row with less columns mentioned in "into table(col1,
col2, col3,..." than the columns that exist on the table, on 1.10.1 it
returns an error saying "INSERT has more expressions than target columns"
(the error is translated so maybe the text is not exactly like this).
Although, when this is insert is done on our test environment, where the
version is 1.10.3 it works fine. The tables have the same columns and so 
on.

Do you think this is really a matter of the different versions or am I
missing something else?
If this is in fact due to the version, can I upgrade the version without 
any

problem and is there any guide to do so?

Have also confirmed that the values are as many as the columns specified 
on

the "into table(col1, col2, col3,..."...

Thank you very much.

Best regards,
Nuno
--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Insert-row-in-1-10-1-and-1-10-3-tp3281512p3281512.html

Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql 



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Insert row in 1.10.1 and 1.10.3

[ndias]

Oliveiros,

Thank you for your reply.

I was debugging and printing the query also for myself and it was indeed
something I was missing. The COM where the query is done was correct in
number of values but the last field had a wrong value and was arriving there
with a ',' in it, which caused the values to be 1 more. Different
application dlls version between client and test environment...

Sorry to trouble you and thanks again.

Best regards,
Nuno
-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Insert-row-in-1-10-1-and-1-10-3-tp3281512p3281562.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Insert row in 1.10.1 and 1.10.3

[Thomas Kellerer]

ndias, 26.11.2010 17:22:

When doing a insert row with less columns mentioned in "into table(col1,
col2, col3,..." than the columns that exist on the table, on 1.10.1 it
returns an error saying "INSERT has more expressions than target columns"
(the error is translated so maybe the text is not exactly like this).
Although, when this is insert is done on our test environment, where the
version is 1.10.3 it works fine. The tables have the same columns and so on.



What versions are you talking about?
PostgreSQL is currently at 9.0.1, the previous version was 8.4.something
I doubt there ever was a Version 1.10.3 of PostgreSQL

Regards
Thomas


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Insert row in 1.10.1 and 1.10.3

[Guillaume Lelarge]

Le 26/11/2010 18:14, Thomas Kellerer a écrit :
> ndias, 26.11.2010 17:22:
>> When doing a insert row with less columns mentioned in "into table(col1,
>> col2, col3,..." than the columns that exist on the table, on 1.10.1 it
>> returns an error saying "INSERT has more expressions than target columns"
>> (the error is translated so maybe the text is not exactly like this).
>> Although, when this is insert is done on our test environment, where the
>> version is 1.10.3 it works fine. The tables have the same columns and
>> so on.
> 
> 
> What versions are you talking about?
> PostgreSQL is currently at 9.0.1, the previous version was 8.4.something
> I doubt there ever was a Version 1.10.3 of PostgreSQL
> 

This looks like a pgAdmin release. There was a 1.10.1 and a 1.10.3,
which are a bit old now, and unmaintained.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Insert row in 1.10.1 and 1.10.3

[ndias]

You are both right, the Postgres version is 8.4, 1.10.3 is the pgAdmin
version. 

Sorry the trouble, as you can see I am a newbie at Postgres. :)

Best to all,
Nuno Dias
-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Insert-row-in-1-10-1-and-1-10-3-tp3281512p3281580.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql