How do i generate sums from two tables and then join the results together without duplicates in SQL?

Question

To explain more in detail- I have two tables A & B. Employee ID is the common column between both the tables. I need to join the 2 tables to compare the FTEs in the 2 tables.

Sample table
Table A:
Emp_ID    |S     Period  |s   FTE   
1                201701      0.5
1                201701      0.5
2                201702      0.7
3                201702       1

Table B
Emp_ ID  |S    Period |s     FTE
1              201701         1
2              201702        0.5
3              201702        0.7

FTEs need to be summed and grouped by period and emp_id These 2 tables need to be joined and the result should look something like this

Emp_ID   Period      FTE (Table A)      FTE (Table B)
1              201701       1                           1
2              201702       0.7                       0.5
3              201702      1                             0.7

query

select * from
( select f.Employee_SK , f.period_nk 
  from adj.PayrollAdjustments p  
  cross join IA_FACT_Payroll f) pf
left join (select period_nk
         , employee_sk
         , sum(CalcClinical_FTE) as sum 
      from IA_FACT_Payroll 
     group 
        by Period_NK
        , Employee_SK ) f on pf.Employee_SK = f.Employee_SK

left join ( select LibPeriodKey
               ,PersonnelMasterKey
              , sum(clinicalFTE) as sumP
           from adj.PayrollAdjustments
           group by LibPeriodKey, PersonnelMasterKey) adj on pf.employee_sk = adj.personnelmasterkey

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, May 15 '17 at 09:41
Possible duplicate of [MySQL: How do I SUM non duplicates values when doing multiples JOINS](http://stackoverflow.com/questions/3345657/mysql-how-do-i-sum-non-duplicates-values-when-doing-multiples-joins) — Shadow, May 15 '17 at 09:49
Result-sets need to be rectangular. What output do you want? — Álvaro González, May 15 '17 at 09:52
So: **employee_sk = PersonnelMasterKey** but does **period_nk = LibPeriodKey** ? How about some sample data? or setup a sqlfiddle — Paul Maxwell, May 15 '17 at 12:09
Thanks . I tried the other link. but, I am still getting duplicates due to the join. — Vimalan Sandhya, May 15 '17 at 13:10
You have a true duplicate in the sample data which may just be a mistake. If one of those rows is removed, and you do the joins correctly you cannit get duplicates in the results. Add you latest query attemptbinto the question. And by the way learn how to format so it looks like a query. — Paul Maxwell, May 15 '17 at 22:37
Ah, I see now what you have done - you have not understood my comment regarding the MASTER TABLES and you have simply cross joined the transaction tables which certainly will end up in a mess. — Paul Maxwell, May 16 '17 at 06:29
@Used_By_Already Okay. I have mentioned the sample tables and the desired result table in the question. Can you please tell me where I have gone wrong ? — Vimalan Sandhya, May 16 '17 at 06:37
look again at my suggested solution, at the top is a different method. in your query this part **( select f.Employee_SK , f.period_nk from adj.PayrollAdjustments p cross join IA_FACT_Payroll f) pf** will cause problems - and it is NOT what I mean by "master tables" — Paul Maxwell, May 16 '17 at 06:41

Paul Maxwell · Answer 1 · 2017-05-16T06:34:42.453

If you do not know if "master tables" for employees and periods exist then you might assume that the summary of the payroll table would cover all employees and periods. If this assumption is true then perhaps this will work:

select
*
from (
    select period_nk
         , employee_sk
         , sum(CalcClinical_FTE) as sum 
      from IA_FACT_Payroll 
     group 
        by Period_NK
        , Employee_SK
    ) iap
left join (
    select LibPeriodKey 
         , PersonnelMasterKey
         , sum(ClinicalFTE) as sumP 
      from adj.PayrollAdjustments 
     group 
        by LibPeriodKey
     , PersonnelMasterKey
    ) adj on iap.employee_sk = adj.PersonnelMasterKey and iap.period_nk = adj.LibPeriodKey

However if there is a master table of employees called employee_master and also that there is a master table of periods called periods_master then you could cross join these tables you get a Cartesian product of all employees and all periods allowing you to outer join the calculations like this:

e.g.

select e.id, p.id
from `employee_master` e
cross join `periods_master` p

From a source of employees and periods such as this, you can simply outer join the 2 queries like this:

select
*
from (
    select e.id e_id, p.id p_id
    from `employee_master` e
    cross join `periods_master` p
     ) ep
left join (
    select period_nk
         , employee_sk
         , sum(CalcClinical_FTE) as sum 
      from IA_FACT_Payroll 
     group 
        by Period_NK
        , Employee_SK
    ) iap on ep.e_id = iap.Employee_SK and ep.p_id = iap.Period_NK
left join (
    select LibPeriodKey 
         , PersonnelMasterKey
         , sum(ClinicalFTE) as sumP 
      from adj.PayrollAdjustments 
     group 
        by LibPeriodKey
     , PersonnelMasterKey
    ) adj on ep.e_id = adj.PersonnelMasterKey and ep.p_id = adj.LibPeriodKey

Thanks for the answer. But, I am unable to compare the sum because the results are getting displayed separately. — Vimalan Sandhya, May 15 '17 at 13:12
How would I know that? I cannot see your screen and you have not provided any data. Plus you didn't answer my question either does **period_nk = LibPeriodKey** ? I cannot magically align data that does not share some common information. Also note: You are supposed to develop some code yourself before asking for help here at SO. What have you tried? (add it to the question) — Paul Maxwell, May 15 '17 at 13:43
Yes, I period_nk equals libperiodkey. I tried to add sample tables in the question but they are not getting displayed in the right format. I have FTEs calculated by 2 tables A & B and I want both the FTEs to be displayed together to enable comparison. Does that explain my question? — Vimalan Sandhya, May 15 '17 at 13:48
Please re-look at how I join the subqueries:: there are 2 columns to join by. (1 the employee and, **2 the period**) — Paul Maxwell, May 15 '17 at 14:14
I tried joining using the 2 columns. But, I am getting two employee ID columns and 2 periods. How to obtain the results in the format which I have given above? — Vimalan Sandhya, May 15 '17 at 18:03

How do i generate sums from two tables and then join the results together without duplicates in SQL?

1 Answers1