[sql] 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

This question is related to sql select join group-by

The answer is


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

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

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

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

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


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to select

Warning: Use the 'defaultValue' or 'value' props on <select> instead of setting 'selected' on <option> SQL query to check if a name begins and ends with a vowel Angular2 *ngFor in select list, set active based on string from object SQL: Two select statements in one query How to get selected value of a dropdown menu in ReactJS DATEDIFF function in Oracle How to filter an array of objects based on values in an inner array with jq? Select unique values with 'select' function in 'dplyr' library how to set select element as readonly ('disabled' doesnt pass select value on server) Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working

Examples related to join

Pandas Merging 101 pandas: merge (join) two data frames on multiple columns How to use the COLLATE in a JOIN in SQL Server? How to join multiple collections with $lookup in mongodb How to join on multiple columns in Pyspark? Pandas join issue: columns overlap but no suffix specified MySQL select rows where left join is null How to return rows from left table not found in right table? Why do multiple-table joins produce duplicate rows? pandas three-way joining multiple dataframes on columns

Examples related to group-by

SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by Count unique values using pandas groupby Pandas group-by and sum Count unique values with pandas per groups Group dataframe and get sum AND count? Error related to only_full_group_by when executing a query in MySql Pandas sum by groupby, but exclude certain columns Using DISTINCT along with GROUP BY in SQL Server Python Pandas : group by in group by and average? How do I create a new column from the output of pandas groupby().sum()?