# JOIN the results of two SELECT statements

## Problem

Is there a way to combine the results of two sql SELECT operations into a single statement? I have a database of tasks in which each record represents a single task with deadlines (and a PALT, which is simply an INT of days from start to finish). The number of days in one’s age is also an INT integer.)

I’d like to create a table that lists each individual at the table, their number of tasks, and how many LATE tasks they have (if any.)

I can simply separate this data into distinct tables, like follows:

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

returning data like:

``````ks        # Tasks
person1   7
person2   3
``````

Then there’s this:

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

which returns:

``````ks        # Late
person1   1
person2   1
``````

Also, I’d like to combine the outcomes of these two choose statements (by the KS)

I’d prefer not to use a temp table, but if it’s the only practical option, I’d like to learn more about utilizing temp tables in this way.

I also tried to count() the number of rows that satisfy a conditional, but I couldn’t figure out how to accomplish it. That would also work if it is possible.

Addendum: I’m sorry, but I need columns for KS, Tasks, and Lateness in my findings.

``````KS        # Tasks   # Late
person1   7         1
person2   3         1
person3   2         0  (or null)
``````

I also want someone to show up even if they don’t have any late tasks.

SUM(IF Age > Palt, THEN 1; ELSE 0, END) Late is fine; thank you for your response!

Two select statements work as well, as does joining them using an LEFT JOIN, and I now understand how to join multiple selections in this manner.

## Solution #1

``````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);
``````

## Solution #2

Consider the following:

``````SELECT
*
FROM
(SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks) t1
INNER JOIN
(SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks) t2
ON t1.ks = t2.ks
``````

## Solution #3

Use UNION:

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

If you want duplicates, use UNION ALL:

``````SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks
UNION ALL
SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks
``````

## Solution #4

If Age and Palt are both columns in the same table, you can count(*) all tasks and total only the ones that are late, as shown below:

``````select ks,
sum(case when Age > Palt then 1 end) late
from Table
group by ks
``````

## Solution #5

For this, you can utilize the UNION ALL keyword.

To do it in T-SQL, go to http://msdn.microsoft.com/en-us/library/ms180026.aspx.

UNION ALL – joins all of the results together.

UNION- Works in a similar way as a Set Union, but doesn’t produce duplicate values.

http://sql-plsql.blogspot.in/2010/05/different-between-union-union-all.html for an illustration of the difference.