[SQL] all the table values equal
Hi list, this is a Friday afternoon question... :) I have a table like this: 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 I'm trying to create a query that say me which id_user, inside a interval of time, has all the values -1 The part if the interval it's not a problem, but I'm fighting with the aggregate... 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] all the table values equal
Howdy , Michele, Give this a try SELECT id_user FROM t_your_table WHERE datetime BETWEEN A -- plug here beginning of interval AND B-- and end here GROUP BY id_user HAVING COUNT(*) = -SUM(value) Then tell me if it gives you what you want Best, Oliveiros Cristina - Original Message - From: "Michele Petrazzo - Unipex" To: Sent: Friday, September 17, 2010 3:50 PM Subject: [SQL] all the table values equal Hi list, this is a Friday afternoon question... :) I have a table like this: 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 I'm trying to create a query that say me which id_user, inside a interval of time, has all the values -1 The part if the interval it's not a problem, but I'm fighting with the aggregate... 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] all the table values equal
Oliveiros d'Azevedo Cristina ha scritto: Howdy , Michele, Give this a try SELECT id_user FROM t_your_table WHERE datetime BETWEEN A -- plug here beginning of interval AND B -- and end here GROUP BY id_user HAVING COUNT(*) = -SUM(value) Then tell me if it gives you what you want Thanks, it works, but... it's really a trickle that exploits the value -1 if I understand how its work. If there is another value where look for? Example 13? Thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
