Many SQL queries with multiple criteria

Question

I am trying to optimize the way I get data from my database to display on the dashboard page for software development.

My database structure is as follows:

• The requirements table, which contains all the different requirements with different fields, but, in essence, the REQ_ID key.
• The task table that contains can contain several tasks with TASK_ID, TASK_NAME (DEV, TEST or RELEASE), TASK_STATUS (not started, completed, blocked), TASK_WINDOW (week1, week2, etc., when the task is completed) and a link to requirement with REQ_I. For example, a requirement may have several tasks for developers, testing tasks, and release tasks, but only dev can be performed for them if all dev tasks associated with the requirement are completed, otherwise it is incomplete

I would like to query these two tables to provide me with a result set that contains individually the numbers DEV Complete, Test Complete and Release Complete. Requirements for each window of the DEV task in one request. I am currently doing a few queries, each of which contains subqueries, and then aggregates the results with PHP, but it only takes 15 seconds for exec. Can someone help me consolidate this into one request>

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

Answer 2

I understand this is an old question, but I checked the test with the following patterns:

Template 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 ...)

2 36% , 1 64%. 1 , .