Thanks for the tip.
I have just entered the SQL statement and it isn't giving me the totals I
want but you have given me something to look up to see if I can use uit to
get what I want. I suppose in programming terms what I am after is:
totsurname=0,totstreet=0,tottown=0,totdistrict=0,tot=0
for each district
for each town
for each street
count surnames within the street
at end of each street report the district,town,street and no. of
surnames
count the number of streets
at the end of each town report the district, town, no. of streets, no. of
surnames
count the number of towns
at the end of each district report the district, no. of towns, no. of
streets, no. surnames
when all rows are processed report the no. of districts, no. of towns, no.
of streets, no. surnames
I was hoping to do this with a clever combination of SELECT, GROUP BY, etc.
Kerry
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 28 November 2006 20:56
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: SELECT...GROUP BY WITHIN GROUP BY
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
