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.
