Jack Lauman wrote:
Sorry... I was having a brain fart. (I use entries in the web.xml file
to generate a dropdown list of cuisines). The field cuisine is part of
the restaurant table. And it does not accept a null value. It should
be split out into it's own table.
I do need to get a SUM of all the values returned by COUNT(*) though.
Here's an example of the results for the query below:
WebsiteName Cuisine COUNT(*)
TOS Afghan 1
TOS African 1
TOS American 64
TOS Argentinean 1
TOS Asian 9
TOS BBQ 7
The SUM of COUNT(*) in this case would be '83'
SELECT w.WebsiteName, r.Cuisine, COUNT(*)
FROM Restaurant r
JOIN RestaurantWebsites w
ON r.RestaurantID = w.RestaurantID
WHERE w.WebsiteName = 'TOW'
GROUP BY w.WebsiteName, r.Cuisine
I don't believe you've specified which version of mysql you have. (Sorry if I
missed it.) If you are using 4.1, you could add "WITH ROLLUP" to the query to
get the total(s).
SELECT w.WebsiteName, r.Cuisine, COUNT(*)
FROM Restaurant r
JOIN RestaurantWebsites w
ON r.RestaurantID = w.RestaurantID
GROUP BY w.WebsiteName, r.Cuisine
WITH ROLLUP;
WebsiteName Cuisine COUNT(*)
TOS Afghan 1
TOS African 1
TOS American 64
TOS Argentinean 1
TOS Asian 9
TOS BBQ 7
TOS NULL 83
TOW Afghan 3
TOW African 2
TOW American 37
TOW Argentinean 2
TOW Asian 13
TOW BBQ 5
TOW NULL 62
NULL NULL 145
At the end of each WebsiteName, you get an extra row with NULL in the Cuisine
column and the subtotal for that group in the COUNT(*) column. At the end,
you get another extra line with 2 NULLs and the grand total in the COUNT(*)
column. See the manual for more
<http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html>.
(I left out your WHERE clause, "WHERE w.WebsiteName = 'TOS'", for my example.
If I'd left it in, the output would be missing the TOW rows, and the grand
total would have been the same as the TOS subtotal.)
If you still have 4.0 (or earlier), you can either save the result of your
query in a temporary table and add the counts with a second query, or simply
add the counts in your application.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
