-- ## 37: Overview for next Milestones ## --

-- Show count of **opened** tickets by type for the next Milestones.

SELECT 
 name AS id, 'milestone' AS _realm,
 (SELECT count(*) FROM ticket 
  WHERE milestone=m.name AND type='enhancement' AND status != 'closed') as Enhancements,
 (SELECT count(*) FROM ticket 
  WHERE milestone=m.name AND type='defect' AND status != 'closed') as Defects,
 (SELECT count(*) FROM ticket 
  WHERE milestone=m.name AND type='task' AND status != 'closed') as Tasks
FROM milestone AS m
WHERE completed = 0
ORDER BY name