-- ## 11: Tickets by Component ## --

  SELECT component, 
    Count(*) AS total, 
    Sum(CASE WHEN status = 'closed' THEN 0 ELSE 1 END) AS opened, 
    Round(Avg(CASE WHEN status = 'closed' THEN 0 ELSE 1 END), 2) AS "ratio opened"
  FROM ticket
  WHERE resolution <> 'invalid' OR resolution IS NULL
  GROUP BY component
  ORDER BY "ratio opened" DESC, total DESC