How do I get a count of each value, based from another table?

Question

I have a volunteers_2009 table that lists all the volunteers and a venues table that lists the venues that a volunteer can be assigned to, they are only assigned to one.

What I want to do, is print out the number of volunteers assigned to each venue.

I want it to print out like this:

Name of Venue: # of volunteers

table: volunteers_2009 columns: id, name, venue_id

table: venues columns: id, venue_name

They relate by volunteers_2009.venue_id = venues.id

This is what I have but it is not working properly.

$sql = "SELECT venues.venue_name as 'Venue', COUNT(volunteers_2009.id) as 'Number Of 
Volunteers' FROM venues ven JOIN volunteers_2009 vol ON 
(venues.id=volunteers_2009.venue_id) GROUP BY venues.venue_name ORDER BY
venues.venue_name ASC";

$result = mysql_query($sql);

while(list($name,$vols) = mysql_fetch_array($result)) {
    print '<p>'.$name.': '.$vols.'</p>';
}

Answer 1

Not a MySQL person so this may be really wrong, but when you give your table an alias, don't you then need to refer to it by that name.

$sql = "SELECT ven.venue_name as 'Venue', COUNT(vol.id) as 'Number Of 
Volunteers' FROM venues ven JOIN volunteers_2009 vol ON 
(ven.id=vol.venue_id) GROUP BY ven.venue_name ORDER BY ven.venue_name ASC";

Answer 2

$query = "SELECT ven.venue_name AS 'Venue', count(*) AS 'Number of venues'
         FROM volunteers_2009 AS vol, venues AS ven WHERE vol.venue_id = ven.id 
         GROUP BY ven.venue_name";