JOIN two SELECT statement results

Question

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  gt  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  gt  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

User · Answer

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  gt  Palt GROUP BY ks  t2 ON t1 ks   t2 ks

User · Answer

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  gt  Palt GROUP BY ks  t2 ON  t1 ks   t2 ks

User · Answer

Use UNION   SELECT ks  COUNT    AS    Tasks  FROM Table GROUP BY ks UNION SELECT ks  COUNT    AS    Late  FROM Table WHERE Age  gt  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  gt  Palt GROUP BY ks

User · Answer

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

User · Answer

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  gt  Palt then 1 end  late   from Table  group by ks

[sql] JOIN two SELECT statement results

Examples related to sql

Examples related to select

Examples related to join

Examples related to group-by