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:

SELECT ks, COUNT(*) AS "Total Tasks" FROM Table GROUP BY ks;

SELECT ks, COUNT(*) AS "Overdue Tasks" FROM Table WHERE Age > Palt GROUP BY ks;

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.

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;

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!