JOIN two SELECT statement results

214
votes

Is it possible to join the results of 2 sql SELECT statements in one statement? I have a database of tasks where each record is a separate task, with deadlines (and a PALT, which is just an INT of days from start to deadline. Age is also an INT number of days.)

I want to have a table which has each person in the table, the number of tasks they have, and the number of LATE tasks they have (if any.)

I can get this data in separate tables easily, like so:

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

returning data like:

ks        # Tasks
person1   7
person2   3

and then I have:

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

which returns:

ks        # Late
person1   1
person2   1

And I want to join the results of these two select statements (by the KS)

I'm trying to avoid using a temp table, but if that's the only practical way to do this, I'd like to know more about using temp tables in this fashion.

I also tried to do some kind of count() of rows which satisfy a conditional, but I couldn't figure out how to do that either. If it's possible, that would work too.

Addendum: Sorry, I want my results to have columns for KS, Tasks, and Late

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

Additionally, I want a person to show up even if they have no late tasks.

SUM(CASE WHEN Age > Palt THEN 1 ELSE 0 END) Late
works well, thanks for this answer!

Two select statements also work, using a LEFT JOIN to join them also works, and I understand now how to join multiple selects in this fashion

5
sqlselectjoingroup-by
You haven't given an example of the expected result. Some answer are therefore concatenating results. Some are joining. Which one do you want? – Phil
Sorry, I want my results to have columns for KS, Tasks, and Late KS # Tasks # Late person1 7 1 person2 3 1 person3 2 0 (or null) Additionally, I want a person to show up even if they have no late tasks. Currently achieving this using the two select statement method with a LEFT JOIN (as opposed to the suggested INNER JOIN, which works but doesn't show persons with no late tasks because they don't exist in the second SELECT Also achieving this with the late column being SUM(CASE WHEN Age > Palt THEN 1 ELSE 0 END) Late – sylverfyre

5 Answers

325
votes
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);
91
votes

Try something like this:

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

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

Or UNION ALL if you want duplicates:

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

If Age and Palt are columns in the same Table, you can count(*) all tasks and sum only late ones like this:

select ks,
       count(*) tasks,
       sum(case when Age > Palt then 1 end) late
  from Table
 group by ks
14
votes

you can use the UNION ALL keyword for this.

Here is the MSDN doc to do it in T-SQL http://msdn.microsoft.com/en-us/library/ms180026.aspx

UNION ALL - combines the result set

UNION- Does something like a Set Union and doesnt output duplicate values

For the difference with an example: http://sql-plsql.blogspot.in/2010/05/difference-between-union-union-all.html