I'm not sure that this is exactly what you want, but I think you can use the WITH ROLLUP modifier:
select district, town, street, surname, count(surname) from test5 group by district asc, town asc, street asc, surname asc WITH ROLLUP Here's a link to the MySQL documentation on WITH ROLLUP http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html Donna "Kerry Frater" <[EMAIL PROTECTED]> 11/28/2006 02:31 PM Please respond to <[EMAIL PROTECTED]> To <mysql@lists.mysql.com> cc Subject SELECT...GROUP BY WITHIN GROUP BY Hope I have the right group. I am working out how to get groups within groups. e.g. I have a table with 4 columns C1,C2,C3 & C4 I am looking to select data so that I can get C1 group item C2 Group item C3 Group Item C4 detail End of C3 Group Item count/totals of C3 End of C2 Group Item count/totals of C2, C3 End of C1 Group item count/totals of C1, C2, C3 to describe the gorups let us say the 4 columns are district,town,street,surname. A full "report" would be all the surnames in surname order within street At the end of each "street" I would also get the number of surnames in that "street" within town At the end of each "town" I would also get the number of "streets" and "surnames" within the town within district At the end of each "district" I would also get the number of "towns", "streets" and "surnames" within the district At the end of selecting all I get the number of "districts", "towns", "streets" and "surnames" Thanks Kerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
