Posted By: Anonymous
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
ks # Late person1 1 person2 1
And I want to join the results of these two
select statements (by the
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.
Sorry, I want my results to have columns for
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
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);