How Can I Combine Two SELECT Statements into a Single Query to Show Task Counts and Late Task Counts?

Combine SELECT statement results into a single query

When working with databases, it is extremely valuable to combine data from multiple data sources for analysis. However, achieving this sometimes presents challenges. In this example, we have two separate SELECT statements, each retrieving specific information from the task database.

The first SELECT statement counts the number of tasks assigned to each person:

<code class="language-sql">SELECT ks, COUNT(*) AS '# Tasks'
FROM Table
GROUP BY ks</code>

The second SELECT statement identifies the number of overdue tasks for each person (tasks whose Age exceeds Palt):

<code class="language-sql">SELECT ks, COUNT(*) AS '# Late'
FROM Table
WHERE Age > Palt
GROUP BY ks</code>

Our goal is to combine the results of these two queries into a table that contains each person's identifier (ks), the total number of tasks they have (# Tasks), and the number of overdue tasks they have (# Late) column.

One way to achieve this is to use the JOIN operation. Specifically, LEFT JOIN allows us to include all rows from the first table (t1) and the corresponding rows from the second table (t2) when the join keys (ks) match. If there is no corresponding row in t2, the result will contain NULL values. This ensures that even people with no overdue tasks will appear in the final results.

The following query implements LEFT JOIN:

<code class="language-sql">SELECT t1.ks, t1.[# Tasks], COALESCE(t2.[# Late], 0) AS [# Late]
FROM 
    (SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks) t1
LEFT JOIN
    (SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks) t2
ON (t1.ks = t2.ks);</code>

The COALESCE function handles potential NULL values ​​in [# Late], ensuring a 0 value is returned. This will produce the desired output table:

<code>KS        # Tasks   # Late
person1   7         1
person2   3         1
person3   2         0  </code>

The above is the detailed content of How Can I Combine Two SELECT Statements into a Single Query to Show Task Counts and Late Task Counts?. For more information, please follow other related articles on the PHP Chinese website!