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