Solved

SQL Two Table Joins With Group By

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

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