title: SQL generating multiple count columns
body: <p>I have developed some SQL to generate statistics on our local tickets database table (SQL Server 2008).  As you can see from my code, I want to select from TICKETS, joining group to get the group name, grouping by group code as well as year/month.  </p>
  
  <p>I want to create totals (counts) for how many tickets were open, closed, closed outside of sla (past due date), and ultimately the SLA%.</p>
  
  <p>This code works, however I'm not happy with having to code all the nested (select counts); this seems like not a good strategy with multiple re-scans.</p>
  
  <p>Is there a better design for generating multiple "counts" columns from a single select on a table ... or would this be the standard approach?</p>
  
  <pre><code>select  g.group_name as [Group],
          year(tm.date_open) as Year,
          month(tm.date_open) as Month,
          COUNT(*) as [Tickets Opened],
  
          (select COUNT(*)
           from TICKETS tm2
           where tm2.group_code = tm.group_code
           and year(tm2.COMPLETION_DATE) = year(tm.date_open)
           and month(tm2.COMPLETION_DATE) = month(tm.date_open)
          ) as [Tickets Closed],
  
          (select COUNT(*)
           from TICKETS tm2
           where tm2.group_code = tm.group_code
           and year(tm2.COMPLETION_DATE) = year(tm.date_open)
           and month(tm2.COMPLETION_DATE) = month(tm.date_open)
           and tm2.[COMPLETION_DATE] &lt;= tm2.[DUE_DATE:]
          ) as [Closed Within SLA],   
  
          (select COUNT(*)
           from TICKETS tm2
           where tm2.group_code = tm.group_code
           and year(tm2.COMPLETION_DATE) = year(tm.date_open)
           and month(tm2.COMPLETION_DATE) = month(tm.date_open)
           and tm2.[COMPLETION_DATE] &gt; tm2.[DUE_DATE:]
          ) as [Closed Outside SLA]   --service level agreement
  
  
  from TICKETS tm
  left join GROUPS g on g.group_code = tm.group_code
  where g.group_code in ('techs', 'reps', 'phone')
  
  and year(tm.date_open) = 2015
  --and month(tm.date_open) = 3 -- specific month
  
  group by tm.group_code, g.group_name, year(tm.date_open), month(tm.date_open)
  
  order by g.group_name, year(tm.date_open), month(tm.date_open)
  </code></pre>
  
  <p>I am also going to want to add a SLA% column which will be ([Closed Within SLA] / [Tickets Closed]) * 100.  But as I see it from my current design, I would have to add redundant nested SELECTS for another column, such as ...</p>
  
  <pre><code>(
      cast((select COUNT(*)
       from TICKETS tm2
       where tm2.group_code = tm.group_code
       and year(tm2.COMPLETION_DATE) = year(tm.date_open)
       and month(tm2.COMPLETION_DATE) = month(tm.date_open)
       and tm2.[COMPLETION_DATE] &lt;= tm2.[DUE_DATE:]
      ) as decimal) /
      (select COUNT(*)
       from TICKETS tm2
       where tm2.group_code = tm.group_code
       and year(tm2.COMPLETION_DATE) = year(tm.date_open)
       and month(tm2.COMPLETION_DATE) = month(tm.date_open)
      )
  ) * 100 as [SLA%]   
  </code></pre>