JOIN two SELECT statement results

sql select join group-by

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

You haven't given an example of the expected result. Some answer are therefore concatenating results. Some are joining. Which one do you want?

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. Currently achieving this using the two select statement method with a LEFT JOIN (as opposed to the suggested INNER JOIN, which works but doesn't show persons with no late tasks because they don't exist in the second SELECT Also achieving this with the late column being SUM(CASE WHEN Age > Palt THEN 1 ELSE 0 END) Late

Willian Kirsch

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

This works well, though I didn't specify that I want a LEFT JOIN so that records show up even if they have 0 late tasks.

Accepted this answer because it answers the question I asked best, and it's formatted to be a great reference on JOINing SELECT statements :)

Excellent answer, but can someone tell is this type of 'solution' considered to expensive or it just depends on what you are using it for?

@petrosmm I was asking myself the same thing but I guess it comes down to the size of the records and how you indexed your table. Then you should also compare this query to running multiple queries at the same time using EXPLAIN ANALYZE

Mithrandir

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

Worked for me! Thanks! I also tried using multiple querys (multiple Joins) and it also woks great. If someone is wondering you can just add more JOINs after the last "ON" in the example, and keep using the sequense: ON .... X JOIN (QUERY N -1) ON Y = Z X JOIN (QUERY N) ON Y = Z

aF.

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

Union or Union All will have only 2 columns in result. Where as he wants 3 of them

Nikola Markovinović

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

This is EXACTLY what I wanted, but didn't know how look for it. I didn't think of using a SUM(CASE) - thanks.

Baz1nga

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

JOIN two SELECT statement results

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US