How Can I Combine Multiple SELECT Statements to Analyze Task Completion and Lateness?

Efficient Data Analysis: Combining Multiple SELECT Statements

Database queries often require combining data from multiple sources or applying several transformations. Joining the outputs of multiple SELECT statements offers a powerful way to manipulate data and gain valuable insights.

Consider a task database where each record represents a task, including a deadline (PALT) and the task's age (days since start). A task is late if its age surpasses its PALT. Our goal is to generate a summary table showing each person's total task count and the number of overdue tasks.

This can be achieved using two separate SELECT statements:

SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks;

This query counts the total tasks for each person (identified by ks).

SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks;

This query counts the number of late tasks for each person.

To combine these, we use a LEFT JOIN:

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;

This joins the results based on the ks column. COALESCE handles cases where a person has no late tasks, replacing NULL with 0.

The final table shows ks (person ID), # Tasks (total tasks), and # Late (number of late tasks, or 0 if none). This enables a thorough analysis of individual workloads and the percentage of overdue tasks.

The above is the detailed content of How Can I Combine Multiple SELECT Statements to Analyze Task Completion and Lateness?. For more information, please follow other related articles on the PHP Chinese website!