Multiple SQL Counts in with multiple criteria

I've been trying to optimise the way I retrieve data from my database for display on a "dashboard" type of page for software development

My database structure is as follows:

• Requirements Table that contains all the various requirements with various fields, but importantly a REQ_ID as key.
• Tasks Table that contains can contain multiple tasks with a TASK_ID, TASK_NAME (DEV, TEST OR RELEASE), TASK_STATUS (Not Started, Complete, Blocked), TASK_WINDOW (Week1, Week2, .. etc. when task was completed) and a link back to a requirement with REQ_I. For example, a requirement may have multiple dev tasks, test tasks and release tasks but for can only be dev complete if all the dev tasks related to a requirement is complete, otherwise it is incomplet开发者_开发知识库e

I would like to query these two tables to provide me a results set that contains individually the number DEV Complete, Test Complete and Release Complete requirements per DEV task window in a single query. I'm currently performing multiple query each containing subqueries and then aggregating the results with PHP, however this in total takes 15 sec to exec, Can anybody please help me in consolidating this into a single query>

---

SELECT r.REQ_ID, 
       SUM(CASE WHEN t.TASK_NAME = 'DEV' THEN 1 ELSE 0 END) AS DevComplete,
       SUM(CASE WHEN t.TASK_NAME = 'TEST' THEN 1 ELSE 0 END) AS TestComplete,
       SUM(CASE WHEN t.TASK_NAME = 'RELEASE' THEN 1 ELSE 0 END) AS ReleaseComplete
    FROM Requirements r
        INNER JOIN Tasks t
            ON r.REQ_ID = t.REQ_ID
    WHERE t.TASK_STATUS = 'Complete'
    GROUP BY r.REQ_ID

---

I realize this is an old question but I ran a test with the following patterns:

Pattern 1:

SELECT
 [Count1] = SUM(CASE WHEN ... THEN 1 ELSE 0 END),
 [Count2] = SUM(CASE WHEN ... THEN 1 ELSE 0 END)
FROM
 [Table]
GROUP BY
 [Field]

Pattern 2:

SELECT
  [COUNT1] = (SELECT COUNT(*) FROM [Table] WHERE ...),
  [Count2] = (SELECT COUNT(*) FROM [Table] WHERE ...)

In my case, when running both queries, pattern 2 took 36 % of the time and pattern 1 took 64%. To me, pattern 1 looks more elegant, but it didn't perform nearly as well in my scenario.