How to join 2 tables so you can sum fields in both tables correctly

Question

I am joining 2 tables and summing 3 fields, 2 from 1 table and 1 from the second. However the tables have multiple records and it is giving me incorrect totals (sums). I know what is wrong but do not know how to write the SQL to get the correct result.

Basically the totals are doubled because of 2 records for CCNum 001. So obviously the SQL is incorrect but what would be the correct SQL?

No idea what SQL would look like.

SELECT  AETCC.PayPeriodID, EI.ADP_ID, EI.LastName + ', ' 
 + EI.FirstName AS EmpName, AETCC.LocationNum 
 + '-' + AETCC.CostCenterNum AS CostString, 
 AETCC.LocationNum AS Location, AETCC.CostCenterNum AS CostCenter, 
 AllocationType, sum(AD.TotalHoursWorked) as TotalHoursWorked, 
 sum(AD.TotalPercentWorked) as TotalPercentWorked, 
 sum(AETCC.ExpenseAmount) AS ExpAmt
        FROM    dbo.AllocatedExpenseToCostCenter AS AETCC 
        INNER JOIN
            dbo.AllocationData AS AD 
                ON AETCC.ADP_ID = AD.ADP_ID 
                AND AETCC.CostCenterNum = AD.CostCenterNum 
                AND AETCC.LocationNum = AD.LocationNum 
                AND AETCC.PayPeriodID = AD.PayPeriodID 
        INNER JOIN
            dbo.EmployeeInformation AS EI 
                ON AETCC.ADP_ID = EI.ADP_ID
        WHERE        (AETCC.ExpenseAccount = '8000')
--      GROUP BY AETCC.PayPeriodID, EI.ADP_ID, EI.FirstName, EI.LastName,
--      AETCC.LocationNum, AETCC.CostCenterNum
        GROUP BY AETCC.PayPeriodID, AllocationType, AETCC.LocationNum,
        AETCC.CostCenterNum, EI.ADP_ID, EI.FirstName, EI.LastName

Data in first table:

PayPrd,LocNum,CCNum,ExpAcct,ExpenseAmount,ADP_ID,HoursWorked
1921,024,001,8000,175.2496,2063,0.63
1921,024,001,8000,1810.2617,2063,67.49
1921,024,013,8000,93.3417,2063,0.18

Data in second table:

PayPrd,LocNum,CCNum,Hours,%,ADP_ID
1921,024,001,7.50,0.0843,2063
1921,024,001,77.50,87.0787,2063
1921,024,013,4.00,0.0449,2063

Current Results:

PayPrd,ADP_ID,EmpName,CostString,LocNum,CCNum,Hours,%,ExpAmt
1921,2063,King; Tom,024-001,024,001,170.00,174,3971.0226
1921,2063,King; Tom,024-013,024,013,4.00,0.4,93.3417

Thanks for the pointers. I was trying to paste example output . It looked formatted before posting but after posting it appeared to be unformatted and looked horrible. Tat is why i changed it to CSV. Still trying to figure out how to use the forum. — Darrell, Jun 13 '19 at 12:36
It's good you give text CSVs but they're not tabular or "cut & paste & runnable code". Click on 'Text to DDL' at sqlfiddle.com. (But % is not a valid column name.) PS When you code/debug add to working code & get parts working separately then combine them. To describe a result: Say enough that someone could go away & come back with a solution. When giving a (base or query result) relation(ship)/association or table, say what a row says about the business situation in terms of its column values when it is in the table. [Re queries & specifications.](https://stackoverflow.com/a/33952141/3404097) — philipxy, Jun 13 '19 at 13:20
Your post is a common error where people want the join on a shared key of some aggregations (each possibly involving joining) but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations. This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. — philipxy, Jun 13 '19 at 13:21
Possible duplicate of [Strange duplicate behavior from GROUP\_CONCAT of two LEFT JOINs of GROUP\_BYs](https://stackoverflow.com/questions/45250646/strange-duplicate-behavior-from-group-concat-of-two-left-joins-of-group-bys) — philipxy, Jun 13 '19 at 13:26

score 1 · Accepted Answer · answered Jun 11 '19 at 21:38

Sum from each table individually, then join the results.

SELECT  AETCC.PayPeriodID, EI.ADP_ID, EI.LastName + ', ' 
 + EI.FirstName AS EmpName, AETCC.LocationNum 
 + '-' + AETCC.CostCenterNum AS CostString, 
 AETCC.LocationNum AS Location, AETCC.CostCenterNum AS CostCenter, 
 AllocationType, AD.TotalHoursWorked as TotalHoursWorked, 
 AD.TotalPercentWorked as TotalPercentWorked, 
 AETCC.ExpenseAmount AS ExpAmt
      FROM (SELECT PayPeriodID, LocationNum, CostCenterNum, ADP_ID, ExpenseAccount,
              SUM(ExpenseAmount) as ExpenseAmount
            FROM dbo.AllocatedExpenseToCostCenter
            GROUP BY PayPeriodID, LocationNum, CostCenterNum, ADP_ID, ExpenseAccount
           ) AS AETCC 
        INNER JOIN
           (SELECT PayPeriodID, LocationNum, CostCenterNum, ADP_ID,
              SUM(TotalHoursWorked) as TotalHoursWorked,
              SUM(TotalPercentWorked) as TotalPercentWorked
            FROM dbo.AllocationData
            GROUP BY PayPeriodID, LocationNum, CostCenterNum, ADP_ID
           ) AS AD
                ON AETCC.ADP_ID = AD.ADP_ID 
                AND AETCC.CostCenterNum = AD.CostCenterNum 
                AND AETCC.LocationNum = AD.LocationNum 
                AND AETCC.PayPeriodID = AD.PayPeriodID 
        INNER JOIN
            dbo.EmployeeInformation AS EI 
                ON AETCC.ADP_ID = EI.ADP_ID
        WHERE        (AETCC.ExpenseAccount = '8000')

You could also move the WHERE clause inside the AETCC subquery.

Thanks Fred! That works perfectly. I appreciate the expertise. — Darrell, Jun 13 '19 at 12:37

How to join 2 tables so you can sum fields in both tables correctly

1 Answers1