Solved

SQL Two Table Joins With Group By

Posted on 2012-03-28
3
361 Views
Last Modified: 2012-03-28
I have two tables that contain the same FK reference.  I need to write a query that joins result set from both tables and groups them by the same FK.  For example,

SELECT e.attendedOrientation, sum(DATEDIFF(mm,e.hiredDate,GETDATE()))
FROM employee e
      WHERE e.attendedOrientation IS NOT NULL NULL
GROUP BY e.attendedOrientation

Returns
1     100
2     24
3     21

AND

SELECT t.attendedOrientation, sum(DATEDIFF(mm,t.hiredDate,termDate))
FROM term t
      WHERE t.attendedOrientation IS NOT NULL NULL
GROUP BY t.attendedOrientation

Returns
1     14
2     NULL
3     6

So what I need when I add result set together should be
1     114
2     24
3     27

but I get
1     708
2     NULL (There are no id 2 in ts table)
3     87

Can anyone help me join results together and get correct results.  This is the last query I attempted

Here is expanded query:
SELECT e.attendedOrientation, sum(DATEDIFF(mm,e.hiredDate,GETDATE()))
        + sum(DATEDIFF(mm,t.hiredDate,terminationDate))
FROM employee e
FULL JOIN termts t ON
      e.attendedOrientation = t.attendedOrientation
      WHERE e.attendedOrientation IS NOT NULL OR t.attendedOrientation IS NOT NULL
GROUP BY e.attendedOrientation
0
Comment
Question by:ICG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 500 total points
ID: 37777276
The easiest way is to do Union like below:
SELECT x.attendedOrientation, sum(x.mySum) as mySum from (
SELECT e.attendedOrientation, sum(DATEDIFF(mm,e.hiredDate,GETDATE())) mySum
FROM employee e
      WHERE e.attendedOrientation IS NOT NULL NULL
GROUP BY e.attendedOrientation
UNION ALL
SELECT t.attendedOrientation, sum(DATEDIFF(mm,t.hiredDate,termDate))
FROM term t
      WHERE t.attendedOrientation IS NOT NULL NULL
GROUP BY t.attendedOrientation) x
GROUP BY x.attendedOrientation

---
If you find it running very slow, then we can try some different way also.
0
 

Expert Comment

by:lmduffy
ID: 37777377
Scalar Functions-Horizontal Math:

 3 + 0                                 = 3
 3 + NULL                         = NULL
 3 + Coalesce( NULL, 0 )  = 3

Aggregate Functions-Vertical Math: ( Column functions )

 Sum( NULL, NULL )   = NULL
 Sum( 3, NULL )          = 3

Try removing the WHERE clause and accommodating the NULLs:

SELECT e.attendedOrientation,
       Coalesce( sum( DATEDIFF(mm,e.hiredDate,GETDATE()) ), 0 )
        + Coalesce( sum( DATEDIFF(mm,t.hiredDate,terminationDate) ), 0 )
FROM employee e
FULL JOIN termts t ON
      e.attendedOrientation = t.attendedOrientation
GROUP BY e.attendedOrientation

Open in new window


edit: Check out your vendor implementation, but this is how it works on mine (DB2-iSeries)
0
 
LVL 1

Author Closing Comment

by:ICG
ID: 37777644
This solution worked perfectly!    The correct totals were present in the result set.  I was unaware that you could go a second group by after union and a third to combine all the information.  Great solution.  You saved me some headaches.
0

Featured Post

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question