SQL Two Table Joins With Group By

Posted on 2012-03-28
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

1     100
2     24
3     21


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

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
Question by:ICG

Accepted Solution

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
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.

Expert Comment

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)

Author Closing Comment

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.

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity and sql server 4 36
Create snapshot on MSSQL 2012 3 20
SQL Syntax: How to force case sensitive query? 2 30
SQL Server Error 21 8 25
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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