mysql> select
    ->   q.provider,
    ->   ifnull(q5.count,0) as "patients seen",
    ->   (ifnull(q1.count,0)+ifnull(q2.count,0)+ifnull(q3.count,0)+ifnull(q4.count,0)) as "reminders seen",
    -> #  ifnull(q1.count,0) as "present + response",
    -> #  ifnull(q2.count,0) as "present + no response",
    -> #  ifnull(q3.count,0) as "absent + response",
    -> #  ifnull(q4.count,0) as "absent + no response",
    ->   (ifnull(q1.count,0)/(ifnull(q1.count,0)+ifnull(q2.count,0)))*100 as "response rate",
    ->   (ifnull(q3.count,0)/(ifnull(q1.count,0)+ifnull(q2.count,0)+ifnull(q3.count,0)+ifnull(q4.count,0)))*100 as "badness rate"
    -> from (
    ->   select provider, count(response) as count
    ->   from clinical_summary_reminder_response where date_created>=@start_date and date_created<=@end_date
    ->   group by provider order by location, provider) as q
    -> left outer join (
    ->   select provider, count(response) as count
    ->   from clinical_summary_reminder_response where date_created>=@start_date and date_created<=@end_date
    ->   and present=1 and response!=0
    ->   group by provider order by location) as q1
    ->   on q.provider=q1.provider
    -> left outer join (
    ->   select provider, count(response) as count
    ->   from clinical_summary_reminder_response where date_created>=@start_date and date_created<=@end_date
    ->   and present=1 and response=0
    ->   group by provider order by location) as q2
    ->   on q.provider=q2.provider
    -> left outer join (
    ->   select provider, count(response) as count
    ->   from clinical_summary_reminder_response where date_created>=@start_date and date_created<=@end_date
    ->   and present!=1 and response!=0
    ->   group by provider order by location) as q3
    ->   on q.provider=q3.provider
    -> left outer join (
    ->   select provider, count(response) as count
    ->   from clinical_summary_reminder_response where date_created>=@start_date and date_created<=@end_date
    ->   and present!=1 and response=0
    ->   group by provider order by location) as q4
    ->   on q.provider=q4.provider
    -> left outer join (
    ->   select device_user as "provider", count(distinct device_value) as count
    ->   from clinical_summary_device_log where date_created>=@start_date and date_created<=@end_date
    ->   and device_key='StartViewPatient'
    ->   group by device_user) as q5
    ->   on q.provider=q5.provider;
+----------+---------------+----------------+---------------+--------------+
| provider | patients seen | reminders seen | response rate | badness rate |
+----------+---------------+----------------+---------------+--------------+
|      159 |            15 |             12 |      100.0000 |       0.0000 |
|      289 |            14 |             23 |      100.0000 |       0.0000 |
|    68443 |            23 |             33 |       96.6667 |       3.0303 |
|   288180 |            22 |             31 |      100.0000 |       0.0000 |
|      160 |            70 |             66 |      100.0000 |       0.0000 |
+----------+---------------+----------------+---------------+--------------+
5 rows in set (0.04 sec)

mysql>
mysql> select
    ->   q.provider,
    ->   ifnull(q5.count,0) as "patients seen",
    ->   (ifnull(q1.count,0)+ifnull(q2.count,0)+ifnull(q3.count,0)+ifnull(q4.count,0)) as "drugs seen",
    -> #  ifnull(q1.count,0) as "present + change",
    -> #  ifnull(q2.count,0) as "present + no change",
    -> #  ifnull(q3.count,0) as "absent + change",
    -> #  ifnull(q4.count,0) as "absent + no change",
    -> (ifnull(q1.count,0)/(ifnull(q1.count,0)+ifnull(q2.count,0)))*100 as "change rate",
    ->   (ifnull(q3.count,0)/(ifnull(q1.count,0)+ifnull(q2.count,0)+ifnull(q3.count,0)+ifnull(q4.count,0)))*100 as "badness rate"
    -> from (
    ->   select provider, count(status) as count
    ->   from clinical_summary_medication_response where date_created>=@start_date and date_created<=@end_date
    ->   group by provider order by location, provider) as q
    -> left outer join (
    ->   select provider, count(status) as count
    ->   from clinical_summary_medication_response where date_created>=@start_date and date_created<=@end_date
    ->   and present=1 and status!=0
    ->   group by provider order by location) as q1
    ->   on q.provider=q1.provider
    -> left outer join (
    ->   select provider, count(status) as count
    ->   from clinical_summary_medication_response where date_created>=@start_date and date_created<=@end_date
    ->   and present=1 and status=0
    ->   group by provider order by location) as q2
    ->   on q.provider=q2.provider
    -> left outer join (
    ->   select provider, count(status) as count
    ->   from clinical_summary_medication_response where date_created>=@start_date and date_created<=@end_date
    ->   and present!=1 and status!=0
    ->   group by provider order by location) as q3
    ->   on q.provider=q3.provider
    -> left outer join (
    ->   select provider, count(status) as count
    ->   from clinical_summary_medication_response where date_created>=@start_date and date_created<=@end_date
    ->   and present!=1 and status=0
    ->   group by provider order by location) as q4
    ->   on q.provider=q4.provider
    -> left outer join (
    ->   select device_user as "provider", count(distinct device_value) as count
    ->   from clinical_summary_device_log where date_created>=@start_date and date_created<=@end_date
    ->   and device_key='StartViewPatient'
    ->   group by device_user) as q5
    ->   on q.provider=q5.provider;
+----------+---------------+------------+-------------+--------------+
| provider | patients seen | drugs seen | change rate | badness rate |
+----------+---------------+------------+-------------+--------------+
|      159 |            15 |         27 |      4.0000 |       0.0000 |
|      289 |            14 |         31 |     29.1667 |       0.0000 |
|    68443 |            23 |         58 |     19.6078 |       3.4483 |
|   288180 |            22 |         60 |     19.5652 |       0.0000 |
|      160 |            70 |        131 |      5.8333 |       0.0000 |
+----------+---------------+------------+-------------+--------------+
5 rows in set (0.12 sec)