How to Combine Task and Late Task Counts for Individuals Using SQL Joins?

Consolidating Data from Multiple SQL Queries

Database management often requires integrating data from various sources or tables. A common method is using SQL JOIN operations to combine results from multiple SELECT statements.

This example demonstrates how to combine data from two queries: one counting total tasks and another counting overdue tasks for each individual. The aim is to create a table showing each individual's task count and overdue task count.

The first query calculates each individual's total task count:

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

This produces a table with ks (individual identifier) and Total Tasks.

The second query counts each individual's overdue tasks:

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

This generates a table with ks and Overdue Tasks.

To merge these results, a LEFT JOIN is used. This ensures all individuals from the first query are included, even if they have no overdue tasks.

The combined query is:

<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>

COALESCE handles cases where individuals lack overdue tasks, replacing NULL with 0.

The final table displays ks, Total Tasks, and Overdue Tasks for each individual.

The above is the detailed content of How to Combine Task and Late Task Counts for Individuals Using SQL Joins?. For more information, please follow other related articles on the PHP Chinese website!