Re: Result from Having count

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