stipe42 wrote:
Jack Lauman wrote:
Given the following query, how can it be modified to return 'Cuisine'(s)
that have no rows as having a count of zero and also return a SUM of
COUNT(*)?
I'm sorry, but I'm having trouble picturing what you are doing. What is a
Cuisine with no rows? I see a Restaurant table and a RestaurantWebsites
table. I don't see a Cuisines table. Is there one? I would have expected
one row per restaurant in a table named Restaurant, but it appears that a
restaurant can have multiple Cuisines, with one row in Restaurant for each
restaurant-cuisine combination. Is that right? Then how does
RestaurantWebsites fit in? From your first query, it appears to link to
certain cuisines of certain restaurants?
In other words, I think we need more information to answer this. Carefully
describe your tables (SHOW CREATE tablename would do) and how they relate to
each other.
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
Thanks.
Jack
After the where clause tack on:
HAVING COUNT(*)>0
This will eliminate the zero rows, but won't get you the sum.
You've got it backwards. There aren't any zero-count rows to eliminate from
the given query, but he wants to change the query to *get* zero-count rows.
I think the only way to get the sum is to either run a second query, or
to sum up the count column in your code as you're looping through the
results.
You can get subtotals and the total using WITH ROLLUP (added in 4.1.1). See
the manual for details
<http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html>.
stipe42
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
