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.

Answer 1

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

Answer 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