Postgres Pro
Downloads
Home   >   mailing lists

Thread: Result from Having count

Result from Having count

From
Condor
Date:
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 ?


Regards,
Hristo Simeonov

Re: Result from Having count

From
Frank Lanitz
Date:
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

Re: Result from Having count

From
Condor
Date:
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.

Re: Result from Having count

From
Frank Lanitz
Date:
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