Combine two tables which different number of rows in SQL server

Question

I have two tables that I want to join to create one final table.

Query 1

select DisplayName, Category, NoOfLevels
, count(Underoverestimate) as OverCount
, Avg(CaseDuration - EstDuration) as ODA
from DSU
where yearid between '2016' and '2018'
and underoverestimate = 'Over'
group by DisplayName, Category, nooflevels

Query 2

select DisplayName, Category, NoOfLevels
, count(Underoverestimate) as UnderCount
, Avg(CaseDuration - EstDuration) as ODA
from DSU
where yearid between '2016' and '2018'
and underoverestimate = 'Under'
group by DisplayName, Category, nooflevels

Query 1 Results

DisplayName|Category     |NoOfLevels|OverCount|ODA
Bran, J.   |Fusion       |Single    |2        |102.5
Bran, J.   |Decompression|          |1        |13
Caron, M.  |Fusion       |Multi     |9        |88.444

Query 2 Results

DisplayName|Category     |NoOfLevels|UnderCount|ODA
Curry, S.  |Fusion       |Multi     |2        |105
Bran, J.   |Fusion       |Single    |1        |115.5
Bran, J.   |Decompression|          |4        |131
Caron, M.  |Decompression|          |5        |66

What I want the end result to have is to keep all unique DisplayName, Catergory, and NoOfLevels but add 'OverCount' and ODA from query 1 and 'UnderCount' and 'ODA' from query 2.

Wanted End Result

DisplayName|Category     |NoOfLevels|OverCount|ODA    |UnderCount|ODA
Bran, J.   |Fusion       |Single    |2        |102.5  |1         |115.5
Bran, J.   |Decompression|          |1        |13     |4         |131
Caron, M.  |Decompression|          |         |       |5         |66
Caron, M.  |Fusion       |Multi     |9        |88.444 |          |
Curry, S.  |Fusion       |Multi     |         |       |5         |66

I attempted to do this by making temp tables with query 1 and 2 and then making a new select statement to report the data that I want.

Select #QueryOne.DisplayName, #QueryOne.Category, 
#QueryOne.NoOfLevels, count(#QueryTwo.UnderCount) as UnderCount
from #QueryOne
join #QueryTwo
on #QueryOne.DisplayName = #QueryTwo.DisplayName
group by #QueryOne.DisplayName, #QueryOne.Category, 
#QueryOne.NoOfLevels
order by #QueryOne.DisplayName, #QueryOne.Category, 
#QueryOne.NoOfLevels

My results which are wrong. (I'm still testing the query so I didn't include all the columns I wanted yet, but in testing I noticed the results are wrong)

DisplayName|Category     |NoOfLevels|UnderCount|
Bran, J.   |Fusion       |Single    |6         |
Caron, M.  |Fusion       |Multi     |9         |
Bran, J.   |Decompression|          |6         |
Curry, S.  |Fusion       |Multi     |12        |
Caron, M.  |Decompression|          |9         |

The first 3 columns look correct, but the 'UnderCount' values are incorrect. The 'COUNT' function in this query is giving me a total count of Bran's rows. Using 'SUM' results in the wrong information too. And finally, if I remove 'COUNT(' then I need to put #QueryTwo.UnderCount in the group by which gives me the following results:

DisplayName|Category     |NoOfLevels|UnderCount|
Bran, J.   |Fusion       |Single    |1         |
Bran, J.   |Fusion       |Single    |2         |
Bran, J.   |Decompression|          |1         |
Bran, J.   |Decompression|          |2         |
Caron, M.  |Decompression|          |3         |
Caron, M.  |Decompression|          |2         |
Caron, M.  |Fusion       |Multi     |3         |
Caron, M.  |Fusion       |Multi     |1         |

I've tried looking for this answer through stackoverflow but haven't found a similar issue, I found a lot of questions that ask about joining two tables but their issues are not the same... I've considered UNION but I cant seem to wrap my head if that's the right next step. I think part of the issue is that query 1 has DisplayNames that aren't in query 2 and vice versa. Making it difficult to Join on??

If I need to clarify more, please let me know, my brain is mush.

FuzzyTree · Accepted Answer · 2017-08-04T20:51:36.023

2

Try using a full join to get all unique DisplayName, Category, NoOfLevels rows from both tables

select *
from (query1) t1 
full join (query2) t2 
    on t1.DisplayName = t2.DisplayName
    and t1.Category = t2.Category
    and t1.NoOfLevels = t2.NoOfLevels

Another possible solution is to use conditional aggregation without joins

select DisplayName, Category, NoOfLevels
, count(case when underoverestimate = 'Over' then Underoverestimate end) as OverCount
, count(case when underoverestimate = 'Under' then Underoverestimate end) as UnderCount
, Avg(case when underoverestimate = 'Over' then CaseDuration - EstDuration end) as ODA
, Avg(case when underoverestimate = 'Under' then CaseDuration - EstDuration end) as UDA
from DSU
where yearid between '2016' and '2018'
and underoverestimate IN ( 'Over' , 'Under' )
group by DisplayName, Category, nooflevels

edited Aug 04 '17 at 20:51

answered Aug 04 '17 at 20:45

FuzzyTree

32,014
3
54
85

1

I like the second solution more :) – BICube Aug 04 '17 at 20:55
The first query provides all necessary information that I wanted, however, it becomes difficult to select specific columns since there are some DisplayNames in t1 and others in t2, so using t1.displaynames does not present all combinations. However, the second query you presented works perfectly! I will be adding that thought process to my previous work! Thank you! – CandleWax Aug 07 '17 at 15:19

score 0 · Answer 2 · answered Aug 04 '17 at 20:52

You can use full join to get results from both table. For more information please visit https://www.w3schools.com/sql/sql_join_full.asp

SELECT
  *
FROM (SELECT
  DisplayName,
  Category,
  NoOfLevels,
  COUNT(Underoverestimate) AS OverCount,
  AVG(CaseDuration - EstDuration) AS ODA
FROM DSU
WHERE yearid BETWEEN '2016' AND '2018'
AND underoverestimate = 'Over'
GROUP BY DisplayName,
         Category,
         nooflevels) a
FULL OUTER  JOIN (SELECT
  DisplayName,
  Category,
  NoOfLevels,
  COUNT(Underoverestimate) AS UnderCount,
  AVG(CaseDuration - EstDuration) AS ODA
FROM DSU
WHERE yearid BETWEEN '2016' AND '2018'
AND underoverestimate = 'Under'
GROUP BY DisplayName,
         Category,
         nooflevels) b
  ON a.DisplayName = b.DisplayName
  AND a.Category = b.Category
  AND a.NoOfLevels = b.NoOfLevels

Combine two tables which different number of rows in SQL server

2 Answers2