help with query please ?

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 ========================================================

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. Do you mean something like SELECT COUNT(*) students, clubs_per_student FROM (SELECT first_name, last_name, COUNT(*) clubs_per_student FROM club_members GROUP BY 1,2 ) GROUP BY clubs_per_student ORDER BY clubs_per_student ? Note this assumes that there are no duplicate first_name, last_name, club_name rows. It might not be that concise a report if there are a lot of clubs as clubs_per_student might have a lot of values. -- Ian The Hotmail address is my spam-bin. Real mail address is iang at austonley org uk