CODEWITHSUNDEEP
phpmysql
Brad
Brad

Reputation: 12262

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

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>';
}

Upvotes: 1

Views: 230

Answers (2)

tvanfosson
tvanfosson

Reputation: 532435

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";

Upvotes: 2

Christian C. Salvad&#243;
Christian C. Salvad&#243;

Reputation: 827266

$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";

Upvotes: 0

Related Questions