[UPHPU] adding a count to an MySQL query

[Randy Moller]

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
> UPHPU at uphpu.org
> 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
>
>