CODEWITHSUNDEEP
sqlselectjoingroup-by
sylverfyre
sylverfyre

Reputation: 3129

JOIN two SELECT statement results

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

Upvotes: 277

Views: 999693

Answers (5)

Phil
Phil

Reputation: 42991

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

Upvotes: 423

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

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

Upvotes: 23

aF.
aF.

Reputation: 66687

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

Upvotes: 70

Mithrandir
Mithrandir

Reputation: 25337

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

Upvotes: 129

Baz1nga
Baz1nga

Reputation: 15579

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

Upvotes: 19

Related Questions