Home >Database >Mysql Tutorial >How to Combine Multiple SQL SELECT Statements to Count Tasks and Late Tasks?
Combining Multiple SQL SELECT Statements: A Practical Example
This guide demonstrates how to efficiently combine the results of multiple SQL SELECT
statements into a single, comprehensive table. We'll use a task management database as an example. Each task record includes details like deadlines, days until deadline (PALT), and age (days since creation). The goal is to generate a report showing, for each person, their total task count and the number of overdue tasks.
Individual queries to count total tasks and late tasks might look like this:
<code class="language-sql">SELECT ks, COUNT(*) AS "Total Tasks" FROM Table GROUP BY ks;</code>
<code class="language-sql">SELECT ks, COUNT(*) AS "Overdue Tasks" FROM Table WHERE Age > Palt GROUP BY ks;</code>
To merge these results, we use a LEFT JOIN
. This ensures all persons from the total task count are included, even if they have no overdue tasks.
<code class="language-sql">SELECT t1.ks, t1."Total Tasks", COALESCE(t2."Overdue Tasks", 0) AS "Overdue Tasks" FROM (SELECT ks, COUNT(*) AS "Total Tasks" FROM Table GROUP BY ks) t1 LEFT JOIN (SELECT ks, COUNT(*) AS "Overdue Tasks" FROM Table WHERE Age > Palt GROUP BY ks) t2 ON t1.ks = t2.ks;</code>
The COALESCE
function handles cases where a person has no overdue tasks (resulting in NULL
from the LEFT JOIN
), replacing NULL
with 0 for clarity. The final table will show ks
(person ID), "Total Tasks", and "Overdue Tasks". The LEFT JOIN
ensures all individuals are listed, regardless of whether they have overdue tasks.
The above is the detailed content of How to Combine Multiple SQL SELECT Statements to Count Tasks and Late Tasks?. For more information, please follow other related articles on the PHP Chinese website!