sql server 2008 - SQL generating multiple count columns -

i have developed sql generate statistics on our local tickets database table (sql server 2008). can see code, want select tickets, joining group group name, grouping group code year/month.

i want create totals (counts) how many tickets open, closed, closed outside of sla (past due date), , sla%.

this code works, i'm not happy having code nested (select counts); seems not strategy multiple re-scans.

is there better design generating multiple "counts" columns single select on table ... or standard approach?

select  g.group_name [group],         year(tm.date_open) year,         month(tm.date_open) month,         count(*) [tickets opened],          (select count(*)          tickets tm2          tm2.group_code = tm.group_code          , year(tm2.completion_date) = year(tm.date_open)          , month(tm2.completion_date) = month(tm.date_open)         ) [tickets closed],          (select count(*)          tickets tm2          tm2.group_code = tm.group_code          , year(tm2.completion_date) = year(tm.date_open)          , month(tm2.completion_date) = month(tm.date_open)          , tm2.[completion_date] <= tm2.[due_date:]         ) [closed within sla],             (select count(*)          tickets tm2          tm2.group_code = tm.group_code          , year(tm2.completion_date) = year(tm.date_open)          , month(tm2.completion_date) = month(tm.date_open)          , tm2.[completion_date] > tm2.[due_date:]         ) [closed outside sla]   --service level agreement   tickets tm left join groups g on g.group_code = tm.group_code g.group_code in ('techs', 'reps', 'phone')  , year(tm.date_open) = 2015 --and month(tm.date_open) = 3 -- specific month  group tm.group_code, g.group_name, year(tm.date_open), month(tm.date_open)  order g.group_name, year(tm.date_open), month(tm.date_open) 

i going want add sla% column ([closed within sla] / [tickets closed]) * 100. see current design, have add redundant nested selects column, such ...

(     cast((select count(*)      tickets tm2      tm2.group_code = tm.group_code      , year(tm2.completion_date) = year(tm.date_open)      , month(tm2.completion_date) = month(tm.date_open)      , tm2.[completion_date] <= tm2.[due_date:]     ) decimal) /     (select count(*)      tickets tm2      tm2.group_code = tm.group_code      , year(tm2.completion_date) = year(tm.date_open)      , month(tm2.completion_date) = month(tm.date_open)     ) ) * 100 [sla%]    

yes, can better bunch of subqueries. in single aggregate query, can separate counts of rows satisfy different conditions computing sum() of various expressions evaluate 1 when condition satisfied , 0 otherwise. alternatively, can count() expressions evaluate non-null rows wish count. example, looks may close you're after:

select   g.group_name [group],   year(tm.date_open) year,   month(tm.date_open) month,   count(*) [tickets opened],   count(tm.completion_date) [tickets closed],   sum(case when tm.completion_date <= [due_date:] 1 else 0 end)     [closed within sla],   sum(case when tm.completion_date > [due_date:] 1 else 0 end)     [closed outside sla],   --service level agreement   cast(sum(case when tm.completion_date <= [due_date:] 1 else 0 end)     decimal) / count(tm.completion_date)) [sla%],   tickets tm   left join groups g     on g.group_code = tm.group_code   g.group_code in ('techs', 'reps', 'phone')   , year(tm.date_open) = 2015   -- , month(tm.date_open) = 3 -- specific month group     tm.group_code,     g.group_name,     year(tm.date_open),     month(tm.date_open) order     g.group_name,     year(tm.date_open),     month(tm.date_open)