Re: Result from Having count

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.