You could use a group by based on the event location to get the count by group.
Wade Preston Shearer wrote: > I am stuck on an MySQL query and hoping that someone can help me out. > I have a table that contains events. I have another many-to-many table > that lists each event that a user is registered for. I want to > retrieve all of the events with a count of how many users are > registered for each. > > This query… > > SELECT even.even_id, even.even_title, even.even_status, even.even_type, > UNIX_TIMESTAMP(even.even_start) as start, > UNIX_TIMESTAMP(even.even_end) as end, > UNIX_TIMESTAMP(even.even_modified) as modified, user.user_nickname > FROM dev_cscca.events even > INNER JOIN dev_cscca_cms.users user > ON even.even_modifiedby=user.user_id > WHERE even.even_status in(1,8) > ORDER BY even.even_title asc > > > …returns all of the events. I am not trying to enhance the query so > that it includes the count of how many users are registered (for each > event). > > This… > > SELECT even.even_id, even.even_title, even.even_status, even.even_type, > UNIX_TIMESTAMP(even.even_start) as start, > UNIX_TIMESTAMP(even.even_end) as end, > UNIX_TIMESTAMP(even.even_modified) as modified, user.user_nickname, ( > SELECT COUNT(meev.meev_id) > FROM dev_cscca.members_events meev > WHERE meev.meev_date_scheduled is not NULL > ) as count > FROM dev_cscca.events even > INNER JOIN dev_cscca_cms.users user > ON even.even_modifiedby=user.user_id > WHERE even.even_status in(1,8) > ORDER BY even.even_title asc > > > …returns a count of how many users are registered, but it counts how > many users are registered total, not how many for each of event. I > have tried various joins, but have not been able to find the proper > syntax for narrowing this down. > > _______________________________________________ > > UPHPU mailing list > [email protected] > http://uphpu.org/mailman/listinfo/uphpu > IRC: #uphpu on irc.freenode.net > ------------------------------------------------------------------------ > > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 8.5.430 / Virus Database: 270.14.119/2585 - Release Date: 12/24/09 > 08:11:00 > > _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
