I'm sorry... I was too quick on the draw there ;) Here's what the query should be:
SELECT country, state, count(*) FROM table GROUP BY country, state WITH ROLLUP Hope that's what you were looking for. -Rob Rob Marscher wrote: > "As of MySQL 4.1.1, the |GROUP BY| clause allows a |WITH ROLLUP| > modifier that causes extra rows to be added to the summary output." > So you can do > > SELECT country, state, count(*) FROM table GROUP BY state WITH ROLLUP > > That will give you the state totals, and in the results, where state = > NULL, that's your country total. > > -Rob > > Peter Sawczynec wrote: > >> [Sorry, I sent this note to Talk first in error.] If I have a table >> with fields roughly like: >> >> ID NAME ADDRESS CITY STATE COUNTRY >> >> I would like to do a select that would give me output grouped like so >> (including the quantity): >> >> United States (1589) >> New York (1200) >> Los Angeles (200) >> Dallas (189) >> Maldives (1) >> France (123) >> Paris (111) >> Bordeaux (9) >> Orleans (3) >> >> etc. >> >> Thanks in advance if you've got this at your fingertips. >> >> >> >> Warmest regards, >> >> Peter Sawczynec >> Technology Director >> PSWebcode >> _Design & Interface >> _Ecommerce >> _Database Management >> 646.316.3678 >> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> >> www.pswebcode.com <http://www.pswebcode.com/> >> ------------------------------------------------------------------------ >> >> _______________________________________________ >> New York PHP Community MySQL SIG >> http://lists.nyphp.org/mailman/listinfo/mysql >> >> NYPHPCon 2006 Presentations Online >> http://www.nyphpcon.com >> >> Show Your Participation in New York PHP >> http://www.nyphp.org/show_participation.php >> >> > > -- Rob Marscher Software Engineer [EMAIL PROTECTED] 212.684.9100x17 _______________________________________________ New York PHP Community MySQL SIG http://lists.nyphp.org/mailman/listinfo/mysql NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php
