RE: using count and odd "group by" errors...

John White wrote > > 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! > A simple example should show you the problem Nickname Person_id bob a123 bob c345 bob f987 charlie d444 charlie f123 Your reults will be 3 bob ? 2 charlie ? What do you expect the 3rd column to show you ? If you had used a numeric id, you could use sum() or avg(), but both of thiose are meaningless. Colin Bull c.bull@videonetworks.com sending to informix-list