Home >Database >Mysql Tutorial >How to Combine Multiple SQL SELECT Statements to Count Tasks and Late Tasks?

How to Combine Multiple SQL SELECT Statements to Count Tasks and Late Tasks?

Mary-Kate Olsen
Mary-Kate OlsenOriginal
2025-01-08 22:29:44212browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn