List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 17 2012 3:45pm
Subject:RE: Query help,,,
View as plain text  
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:donw@stripped]
> Sent: Thursday, May 17, 2012 7:37 AM
> To: mysql@stripped
> 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

Thread
Query help,,,Don Wieland17 May
  • RE: Query help,,,Rick James17 May
  • Re: Query help,,,Peter Brawley17 May