SQL - SELECT on table with multiple rows doubling result

Question

I have an issue where I'm selecting from two tables as follows:

select  SUM(tc.WEEKLY_HOURS),
            SUM(pc.var_cash)
from    time_capture tc
LEFT OUTER JOIN PAYMENT_CAPTURE pc on tc.EMP_NO = pc.emp_no
                                                        AND tc.EFFECTIVE_DATE = pc.EFFECTIVE_DATE
where   tc.effective_date = '17 June, 2012'
GROUP BY LEFT(tc.COST_CENTRE, 4)

The two tables contain:

time_capture

emp_no     EFFECTIVE_DATE          weekly_hours
---------- ----------------------- ----------------------
1234     2012-06-17              28

PAYMENT_CAPTURE

emp_no     EFFECTIVE_DATE          INPUT_CODE cost_centre               var_cash
---------- ----------------------- ---------- ------------------------- ----------------------
1234       2012-06-17              KEY        5607.03                   45
1234       2012-06-17              OTHER      5607.03                   19.23

I was expecting the sum(weekly_hours) to return 28 but it returns 56 (below) due (I think) the two rows in payment_capture.

Could some point me in the right direction on this?

Many thanks.

weekly_hours           var_cash
---------------------- ----------------------
56                     64.23

Why are you using a date literal like `'17 June, 2012'`? Please use unambiguous formats that aren't subject to different interpretation by SQL Server based on language and other settings. `'20120617'` is much safer. — Aaron Bertrand, Jun 25 '12 at 14:38
Without knowing more of the design of your system it is difficult to know if this is the right approach, but based on what we know it appears you should use a subquery to sum the data in payment_capture and then join to the subquery in your outer query of time_capture. — Tim Lentine, Jun 25 '12 at 14:44
What do you want the results to be if you have two employees with time for that day? And can there ever be two rows for an emp_no / effective_date combination in the time_capture table? — Aaron Bertrand, Jun 25 '12 at 14:47

score 2 · Accepted Answer · answered Jun 25 '12 at 15:05

You need to pre-aggregate your cash for this using a subquery:

select  
    SUM(tc.WEEKLY_HOURS) as 'Hrs',
    SUM(pc.var_cash) as 'Cash'
from    
    time_capture tc
LEFT OUTER JOIN 
    (
    SELECT 
        Emp_No, 
        Effective_Date, 
        SUM(var_cash) as 'var_cash'
    FROM
        PAYMENT_CAPTURE)
    pc 
        on tc.EMP_NO = pc.emp_no
        AND tc.EFFECTIVE_DATE = pc.EFFECTIVE_DATE
where   
    tc.effective_date = '17 June, 2012'
GROUP BY L
    EFT(tc.COST_CENTRE, 4)

score 1 · Answer 2 · answered Jun 25 '12 at 14:40

1

it is reading it right. Since you are doing a join, it is returning:

 1234 | 2012-06-07 | 28 | 1234 | 2012-06-07 | 5607.03 | 45

 1234 | 2012-06-07 | 28 | 1234 | 2012-06-07 | 5607.03 | 19.23

Therefore, since you are summing, you get 28+28 = 56, and 45+19.23 = 64.23

answered Jun 25 '12 at 14:40

Fallenreaper

10,222
12
66
129

1

Well this is describing why there is a problem with the result, but I was kind of expecting you to continue with a query that gets the right result? – Aaron Bertrand Jun 25 '12 at 14:43
Looks like a MAX is required - looks like redundancy in a non-normalised DB table – Charleh Jun 25 '12 at 14:55
why does it need to be set the query up this way? Why not have 2 queries, 1 to sum the house, and 1 to sum the var_cash. That way you dont have to worry about it. I mean, you could prolly get around and combine both items in a subselect since you can do them seperately. Such as: Select A.hours, B.var_cash from (Select sum(A.hours) from A) join (Select sum(B.var_cash) from B) on A.emp_no = B.emp_no where effective_date = @date; I havent run the query, but since you can have 2 seperate queries do this, why not form 2 queries which will do both parts, and then the top level print it out. – Fallenreaper Jun 25 '12 at 15:01

SQL - SELECT on table with multiple rows doubling result

2 Answers2