Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id EFC711337B65 for ; Tue, 21 Sep 2010 11:46:58 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 74343-05 for ; Tue, 21 Sep 2010 14:46:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mail.postgresql.org (Postfix) with ESMTP id B70121337B60 for ; Tue, 21 Sep 2010 11:46:50 -0300 (ADT) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id SBPTZ9XK; Tue, 21 Sep 2010 15:46:49 +0100 Message-ID: From: "Oliveiros d'Azevedo Cristina" To: "Oliveiros d'Azevedo Cristina" , "Michele Petrazzo - Unipex" Cc: References: <4C93803A.4080706@unipex.it> <438B32316EF6496D9641819BA16447A2@marktestcr.marktest.pt> <4C938D09.3050105@unipex.it> <81C545E3469E4DF58720C282A469E78A@marktestcr.marktest.pt> <4C9868AB.6050200@unipex.it> <5E43A989C2FA4F60B146DAC454BAA21D@marktestcr.marktest.pt> Subject: Re: all the table values equal Date: Tue, 21 Sep 2010 15:46:49 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_02C0_01CB59A4.3456E650" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201009/68 X-Sequence-Number: 34599 This is a multi-part message in MIME format. ------=_NextPart_000_02C0_01CB59A4.3456E650 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Howdy, Michele. Any news? Any progress/problems? If you want to find out all the id_users who don't change value in a = given time interval you can use something like this. SELECT "id_user","value" FROM ( SELECT "id_user", COUNT(*) as total FROM t_yourtable WHERE "datetime" BETWEEN '2010-9-1' -- Interval beginning AND '2010-9-11' -- interval end GROUP BY "id_user" ) as foo NATURAL JOIN ( SELECT "id_user","value",COUNT(*) as total FROM t_yourtable WHERE "datetime" BETWEEN '2010-9-1' -- again, plug interval beginning = here AND '2010-9-11' -- and interval end here GROUP BY "id_user","value" ) as bar This will give you the users who didn't change value on the time = interval. Finally, if you want to look for a specific value just add the clause "WHERE "value" =3D x -- = x=3Dthe specific value you want to look for to the query above. It'll return just the id_user(s) that have just the = "value" x. Please try it out and feel free to get back to me if it didn't work or = if it didn't solve your problem Best, Oliver ----- Original Message -----=20 From: "Oliveiros d'Azevedo Cristina" To: "Michele Petrazzo - Unipex" Cc: Sent: Tuesday, September 21, 2010 11:25 AM Subject: Re: [SQL] all the table values equal >=20 >>> It would help if you explain a little better the background of the >>> problem you're trying to solve. >>> >>> You want to find all the user IDs which have the same value on a >>> given time interval? >>> >>> Is my understanding correct? >>> >> >> Yes. >> >> Re-reading my post I saw that I could explain better! >> >> id_user | value | datetime >> 1 | 1 | xxx >> 1 | 2 | xxx+1 >> 1 | -1 | xxx+2 >> 2 | -1 | xxx >> 2 | -1 | xxx+1 >> 3 | 4 | xxx >> 3 | 10 | xxx+1 >> 3 | 4 | xxx+2 >> 4 | 3 | xxx >> 4 | 3 | xxx+1 >> >> So, the new question: how I can find which id_user has _all_ the = "value" >> that I'm looking for? Say -1 as 3 and I want a id_user=3D2 for the = first >> and for the latter id_user=3D4 >> >=20 > OK, So, analysing your new question, the time interval is still = important?=20 > Or no longer? > So writting your query in english : "I want all users that don't = change=20 > "value" and their value is equal to x ?" >=20 > Or will you need to find all the users that don't change value with = just one=20 > query? And the query would return something like >=20 > id_user | value > 2 | -1 > 4| 3 >=20 > ? > Please advice me on this. >=20 > I am just trying to avoid misunderstanding your requirements and = ending up=20 > with a query that doesn't really do what you need. >=20 > Best, > Oliver=20 >=20 >=20 > --=20 > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql ------=_NextPart_000_02C0_01CB59A4.3456E650 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Howdy, Michele.
Any news?
Any progress/problems?
 
If you want to find out all the = id_users who don't=20 change value in a given time interval
you can use something like = this.
 
 
SELECT "id_user","value"
FROM = (
SELECT=20 "id_user", COUNT(*) as total
FROM t_yourtable
WHERE "datetime" = BETWEEN=20 '2010-9-1'  -- Interval beginning
AND '2010-9-11'  -- = interval=20 end
GROUP BY "id_user"
) as foo
NATURAL JOIN
(
SELECT=20 "id_user","value",COUNT(*) as total
FROM t_yourtable
WHERE = "datetime"=20 BETWEEN '2010-9-1'   -- again, plug interval beginning = here
AND=20 '2010-9-11'  -- and interval end here
GROUP BY=20 "id_user","value"
)  as bar
 
 
This will give you the users who didn't = change=20 value on the time interval.
Finally, if you want to
look for a specific value just add the = clause=20 "WHERE "value" =3D x  -- x=3Dthe specific = value you want to=20 look for
to the query above. It'll return just = the=20 id_user(s) that have just the "value" x.
 
Please try it out and feel free to get = back to me=20 if it didn't work or if it didn't solve your problem
 
Best,
Oliver
 
----- Original Message -----
From: "Oliveiros d'Azevedo Cristina" = <oliveiros.cristina@marktest.pt>
To: "Michele Petrazzo - Unipex" = <michele.petrazzo@unipex.it>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, September 21, 2010 11:25 = AM
Subject: Re: [SQL] all the table values = equal

>
>>> It would help if you explain a little = better the=20 background of the
>>> problem you're trying to=20 solve.
>>>
>>> You want to find all the user IDs = which=20 have the same value on a
>>> given time=20 interval?
>>>
>>> Is my understanding=20 correct?
>>>
>>
>> = Yes.
>>
>>=20 Re-reading my post I saw that I could explain = better!
>>
>>=20 id_user | value | datetime
>> 1 | 1 | xxx
>> 1 | 2 |=20 xxx+1
>> 1 | -1 | xxx+2
>> 2 | -1 | xxx
>> 2 = | -1 |=20 xxx+1
>> 3 | 4 | xxx
>> 3 | 10 | xxx+1
>> 3 | = 4 |=20 xxx+2
>> 4 | 3 | xxx
>> 4 | 3 | = xxx+1
>>
>>=20 So, the new question: how I can find which id_user has _all_ the=20 "value"
>> that I'm looking for? Say -1 as 3 and I want a = id_user=3D2 for=20 the first
>> and for the latter id_user=3D4
>>
> =
>=20 OK, So, analysing your new question, the time interval is still = important?=20
> Or no longer?
> So writting your query in english : "I = want all=20 users that don't change
> "value" and their value is equal to x=20 ?"
>
> Or will you need to find all the users that don't = change=20 value with just one
> query? And the query would return something = like
>
> id_user | value
> 2 | -1
> 4| = 3
>=20
> ?
> Please advice me on this.
>
> I am just = trying=20 to avoid misunderstanding your requirements and ending up
> with = a query=20 that doesn't really do what you need.
>
> Best,
> = Oliver=20
>
>
> --
> Sent via pgsql-sql mailing list=20 (pgsql-sql@postgresql.org)
> To=20 make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql ------=_NextPart_000_02C0_01CB59A4.3456E650--