Re: help with query please ?

Something like: select first_name || last_name as wholename, count(*) as ccount from club_members group by 1 into temp tccount; select count(wholename), ccount from tccount group by 2 order by 2; Or you could do something like select count(wholename), ccount from (select first_name || last_name as wholename, count(*) as ccount from club_members group by 1 ) group by 2 order by 2; Not tested either one. Not sure how you're going to cope with students who aren't in any clubs. On 9 May 2012, at 13:11, Floyd Wellershaus wrote: > I have a table called club_members. It has 3 columns first_name,last_name,club_name > > Write a query to summarize how many clubs each student is in. The Dean wants a concise report so consolidate it to show just the count of students based on the number of clubs they are in. > > Sample report (your numbers and basic styling may differ) > > students, clubs_per_student > > 600, 0 > > 300, 1 > > 200, 2 > > ... > > > > I've tried and can't get a handle around this one. Any help would be greatly appreciated. > > Thanks, > floyd > > > > Floyd Wellershaus > Dba/Sa Informix/Oracle/Linux/Aix > > http://www.linkedin.com/in/floydwellershaus > http://photos.fwellers.com > ======================================================== > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list