RE: using count and odd "group by" errors...
John,
In a group-by query you can only select constants, columns in the
group-by condition and aggregate functions on those columns. This is
because the rows are grouped by your group-by clause and you get one row
per group. Eg
person_id nickname
--------------------
1 john
2 john
3 foobar
Grouping by nickname will give you two groups (john and foobar). In this
context, person_id has lots its meaning.
What you need is a subselect:
select person_id, nickname
from person
where nickname in
(select nickname
from person
group by nickname
having count(nickname) > 1)
HTH,
> -----Original Message-----
> From: owner-informix-list@iiug.org
> [mailto:owner-informix-list@iiug.org] On Behalf Of John White
> Sent: 02 April 2004 06:00
> To: informix-list@iiug.org
> Subject: using count and odd "group by" errors...
>
>
> I've been reading the Informix SQL manual for a while and can't seem
> to find an explanation which clears up an issue I have regarding the
> use of "count."
>
> I'm looking for a way to select rows which have duplicate values of a
> non-key field. If I have a "people" table keyed on person_id, I would
> like to display all people who share a "nickname" with other people.
> Hopefully grouped and ordered by nickname.
>
> I tried:
>
> SELECT COUNT(nickname), nickname
> FROM people
> GROUP BY nickname
> HAVING COUNT(nickname) > 1
> ORDER BY 1 DESC>
> which gives me a nice list of the most frequently used nicknames in
> descending order (and the count, of course).
>
> But when I change the select statement to:
>
> SELECT COUNT(nickname), nickname, person_id>
> I get an error stating that person_id needs to be added to the group
> by list. Then when I do add it, I get no results!
>
> I realize that I'm not grasping something important here. Can someone
> tell me what it is?
>
sending to informix-list