MYSQL count and distinct

software development

#1

If someone can help me come up with a better way to do this, I’d appreciate it. I’m trying to get the DISTINCT city names, but I’d also like to get the number of meetings for each city or state. I could run a separate query for the count, but I could have potentially thousands of cities. My fear is that performance will take a serious hit.

Here are the details:
Table: calendar_cities
-city_id
-state
-city

Table: calendar_meetings
-mid (meeting id)

$bookmark_list = mysql_query(“select DISTINCT m.city_id, c.city_id, c.state, c.city from calendar_meetings as m left join calendar_cities as c on m.city_id = c.city_id where m.meeting_date > (CURDATE() - INTERVAL 1 DAY) order by c.state, c.city”);

I’m using MySQL5. I thought of maybe a nested select.

Thanks
Angela

================================
Angela Gann
CrimsonDryad Web Design Services
Web Design, Custom Software Development
http://www.crimsondryad.com


#2

select m.city_id, c.city_id, c.state, c.city, COUNT(*) from calendar_meetings as m left join calendar_cities as c on m.city_id = c.city_id where m.meeting_date > (CURDATE() - INTERVAL 1 DAY) GROUP BY m.city_id order by c.state, c.city


#3

Thanks a ton! I saw that on the MySQL manual, but for some reason I didn’t think it would be that easy. :slight_smile:

================================
Angela Gann
CrimsonDryad Web Design Services
Web Design, Custom Software Development
http://www.crimsondryad.com