CODEWITHSUNDEEP
sqlsql-server
dtjmsy
dtjmsy

Reputation: 2762

sql aggregating the selection results

I would like to aggregate all of my selection results:

I have a select user result1 from....) returning 2 columns:

user   results1
****   ********
user1  5
user2  8

I also have another query select user result2 from....) returning another 2 columns:

user   results2
****   ********
user1  9
user2  5
user3  15

How would I join the results together with 3 columns to make something like:

user   results1   results2
****   ********   ********
user1  5          9
user2  8          5
user3            15

Upvotes: 3

Views: 58

Answers (3)

Taryn
Taryn

Reputation: 247840

If you aren't sure that the User value exists in both tables, then you should use a FULL JOIN to query the tables. A FULL JOIN or FULL OUTER JOIN (OUTER is optional) will return the rows from both tables, but if the user doesn't exist in one of them, you will still get results:

select 
  [user] = coalesce(r1.[user], r2.[user]),
  r1.results1,
  r2.results2
from result1 r1
full join result2 r2
  on r1.[user] = r2.[user];

See SQL Fiddle with Demo.

The problem with using an INNER JOIN is it will require that the User is in both tables. If you try to use a RIGHT JOIN or LEFT JOIN - then you may still not return all users and their results.

If you have multiple queries that you are trying to combine, then you can use a CTE or subqueries:

;with query1 as
(
  select [user], result1 from results1
),
query2 as
(
  select [user], result2 from results2
)
select 
  [user] = coalesce(q1.[user], q2.[user]),
  q1.result1,
  q2.result2
from query1 q1
full join query2 q2
  on q1.[user] = q2.[user]

Upvotes: 5

Veera
Veera

Reputation: 3492

Try this query: (As you want to combine the result of two separate answers from queries)

SELECT b.User, a.Result1, b.Result2 
from (select user, result1 from....) a
     full outer join (select user, result2 from....) b 
where a.user = b.user

Upvotes: 2

NP3
NP3

Reputation: 662

You should use FULL OUTER JOIN

select b.User, a.Result1, b.Result2 
from table1 A
full outer join table2 b 
where a.user = b.user

a Full outer Join will give you all records only in table a and b + common records.

Upvotes: 2

Related Questions