Result from Having count

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

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.

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