Multiple SQL Counts in with multiple criteria

Question

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 incomplete

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>

Answer 1

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