title: SQL Percentage Group By
body: <p>I need to present a quick report of employee presenters and their client classroom attendance percentages, and I'm trying to figure out the correct query in SQL (for SQL Server).</p>
  
  <p>Clients are scheduled individually for classes, so in table tSchedule, every row has a class name, the time and date of the class, a client name, the class presenter's name, and the client's attendance status (e.g., 'present', 'absent', 'absent w/ excuse','late', etc.)</p>
  
  <p>So I need a SQL query that will output one row for each presenter, with the total number of clients that were scheduled in that presenter's classes within a given date range, the total number who actually showed up (i.e., with an attendance status of 'present' or 'absent w/excuse'), and what percent of the total those "present or excused" clients represent.</p>
  
  <p>Adding in some details per replies below:</p>
  
  <pre><code>tSchedule
  
  class            class_date     Employee_id     client_id       attendance_status
  Basket Weaving   2017-07-13     231             712             Present
  Basket Weaving   2017-07-13     231             121             Present
  Basket Weaving   2017-07-13     231             186             Absent
  Basket Weaving   2017-07-13     231             666             Absent
  Juggling         2017-07-13     900             111             Present
  Juggling         2017-07-13     900             222             Present
  Juggling         2017-07-13     900             333             Present
  Juggling         2017-07-13     900             712             Absent w/Excuse
  
  
  
  Expected Result of Query:
  
  Employee_id Clients Scheduled   Clients Present or Excused  Attendance Rate
  231         4                   2                           50%
  900         4                   4                           100%
  </code></pre>
  
  <p>(Addendum)
   Okay, the query I've ended up using (below) works, but it's ugly and I'm sure not ideal. If anyone knows the most elegant way to get the same results, I'd much appreciate. (@param1 and @param2 are user-entered dates for start and end dates of desired timespan)</p>
  
  <pre><code>Select
     pl.emp_id
     ,e.last_name + ', ' + e.first_name as facilitator
     ,count(pl.emp_id) as total_Count
     ,(select count(*) from planner where emp_id = pl.emp_id
         and visit_status in ('ARRIVED', 'PRESENT WITH JS', 'PRESENT NO JS')
         and plan_date &gt;= @param1
         and plan_date &lt;= @param2) as attended_Count
     ,cast(cast((select count(*) from planner where emp_id = pl.emp_id
         and visit_status in ('ARRIVED', 'PRESENT WITH JS','PRESENT NO JS')
         and plan_date &gt;= @param1
         and plan_date &lt;= @param2) as float) / cast((select count(*) from
         planner where emp_id = pl.emp_id
         and plan_date &gt;= @param1
         and plan_date &lt;= @param2) as float) * 100 as decimal (18,2)) as attendance_percent
  from planner pl inner join employees e on pl.emp_id = e.emp_id
  where pl.program_id = 2
      and pl.visittype_id in (42,173)
      and plan_date &gt;=@param1
      and plan_date &lt;= @param2
  group by pl.emp_id, e.last_name + ', ' + e.first_name
  </code></pre>