Re: [GENERAL] Result from Having count

2012-08-23 Thread Frank Lanitz 
Am 23.08.2012 09:52, schrieb Condor:
 Hello ppl,
 
 I try to make query and see how many ids have more then one row.
 
 few records is:
 
 ids | val | some
  a  | 1   | x
  a  | 1   | v
  b  | 1   | x
  b  | 2   | c
 
 
 I focus on ids and val with:
 
 SELECT ids, val FROM table WHERE ids = 'a' GROUP BY ids, val HAVING
 COUNT(ids)  1;
 
 and result is:
 
 ids | val
  a  |   1
 
 Well in this condition pgsql shold not return me positive result because
 on documentation I read having count work on group clause,
 and when I group these two records based on ids = 'a' they become to one
 row and my condition is if the result after grouping is greeter then 1.
 
 I use postgresql 9.1.4 x64
 
 
 Any one can tell me what I miss ?


Not sure I understand you correct, but maybe count() is working for you.
Maybe you would need some primary key for good values.

cheers,
Frank



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

Re: [GENERAL] Result from Having count

2012-08-23 Thread Condor 

On , Frank Lanitz wrote:

Am 23.08.2012 09:52, schrieb Condor:

Hello ppl,

I try to make query and see how many ids have more then one row.

few records is:

ids | val | some
 a  | 1   | x
 a  | 1   | v
 b  | 1   | x
 b  | 2   | c


I focus on ids and val with:

SELECT ids, val FROM table WHERE ids = 'a' GROUP BY ids, val HAVING
COUNT(ids)  1;

and result is:

ids | val
 a  |   1

Well in this condition pgsql shold not return me positive result 
because

on documentation I read having count work on group clause,
and when I group these two records based on ids = 'a' they become to 
one
row and my condition is if the result after grouping is greeter then 
1.


I use postgresql 9.1.4 x64


Any one can tell me what I miss ?



Not sure I understand you correct, but maybe count() is working for 
you.

Maybe you would need some primary key for good values.

cheers,
Frank



Sorry for my email,
after some thinking I understand my error and change query to:

SELECT COUNT(DISTINCT val), ids FROM table WHERE ids = 'a' GROUP BY ids 
HAVING COUNT(DISTINCT val)  1;


and it's work.

Thanks.
Hristo C.


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

Re: [GENERAL] Result from Having count

2012-08-23 Thread Frank Lanitz 
Am 23.08.2012 10:45, schrieb Condor:
 On , Frank Lanitz wrote:
 Am 23.08.2012 09:52, schrieb Condor:
 Hello ppl,

 I try to make query and see how many ids have more then one row.

 few records is:

 ids | val | some
  a  | 1   | x
  a  | 1   | v
  b  | 1   | x
  b  | 2   | c


 I focus on ids and val with:

 SELECT ids, val FROM table WHERE ids = 'a' GROUP BY ids, val HAVING
 COUNT(ids)  1;

 and result is:

 ids | val
  a  |   1

 Well in this condition pgsql shold not return me positive result because
 on documentation I read having count work on group clause,
 and when I group these two records based on ids = 'a' they become to one
 row and my condition is if the result after grouping is greeter then 1.

 I use postgresql 9.1.4 x64


 Any one can tell me what I miss ?


 Not sure I understand you correct, but maybe count() is working for you.
 Maybe you would need some primary key for good values.

 cheers,
 Frank
 
 
 Sorry for my email,
 after some thinking I understand my error and change query to:
 
 SELECT COUNT(DISTINCT val), ids FROM table WHERE ids = 'a' GROUP BY ids
 HAVING COUNT(DISTINCT val)  1;
 
 and it's work.

At least I was wrong in understanding your request. ;) But glad, you
found a solution.

Cheers,
Frank



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