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.

Asked by sylverfyre

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

Answered by Phil

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

Answered by Mithrandir

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

Answered by aF.

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,
       count(*) tasks,
       sum(case when Age > Palt then 1 end) late
  from Table
 group by ks

Answered by Nikola Markovinović

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.

Answered by Baz1nga

Post is based on https://stackoverflow.com/questions/10538539/join-two-select-statement-results