For (1), ponder: Group-by trick example: Find the most populous city in each state:
SELECT state, city, population, COUNT(*) AS num_cities FROM ( SELECT state, city, population FROM us ORDER BY state, population DESC ) p GROUP BY state ORDER BY state; +-------+-------------+------------+------------+ | state | city | population | num_cities | +-------+-------------+------------+------------+ | AK | Anchorage | 276263 | 16 | | AL | Birmingham | 231621 | 58 | | AR | Little Rock | 184217 | 40 | | AZ | Phoenix | 1428509 | 51 | | CA | Los Angeles | 3877129 | 447 | ... > -----Original Message----- > From: Don Wieland [mailto:d...@pointmade.net] > Sent: Thursday, May 17, 2012 7:37 AM > To: mysql@lists.mysql.com > Subject: Query help,,, > > Hi folks, > > I am trying to compile a query that does statistics on appointments > based on specific criteria. Here is my starting query: > > SELECT > u.user_id, > c.client_id, > c.first_name, > c.last_name, > a.time_start AS stime, > FROM_UNIXTIME(a.time_start,'%Y-%m-%d') AS formatted > > FROM tl_appt a > LEFT JOIN tl_users u ON a.user_id = u.user_id > LEFT JOIN tl_clients c ON a.client_id = c.client_id > LEFT JOIN tl_rooms r on a.room_id = r.room_id > > WHERE a.appt_id IS NOT NULL AND FROM_UNIXTIME(a.time_start,'%Y-%m- > %d') between '2011-05-01' and '2011-12-31' and r.location_id = '2' and > a.user_id IN (14) ORDER BY u.last_name, u.first_name, c.last_name, > c.first_name > > This will return a set of rows where a client may have MORE THEN ONE > appointment. From this set I need to narrow more: > > 1) Only display the first appointment PER Client. (there will be no > duplicate client_id) > > Then once I have that set of rows established, I need to query for two > more result: > > 1) Show New Customers = those where the COUNT of appointments (in the > entire tl_appt table) LESS THAN the stime = 0 > > 2) Show FORMER Customers = those where the COUNT of appointments (in > the entire tl_appt table) LESS THAN the stime > 0 > > I am sure I need a multiple select query, but I am having a hard time > wrapping my head around it. > > Thanks for any feedback. > > Don > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql