My sums are way too huge, is it not possible to do them all at once?

I have a table with colleague info, including their IDs, and a separate table with transaction data (such as revenue).

In the transaction table, there are three fields (code1, code2, code3), they could be blank, or have colleague IDs, etc. I have no control over these tables.

I am trying to accumulate some sums by colleague, so right now I have things like this:

SELECT Colleagues.Name, 
SUM(CASE WHEN Trans1.Code1 IS NOT NULL AND Trans1.RevMth <= 3 THEN Trans1.Rev END) as Sum1, 
SUM(CASE WHEN Trans2.Code2 IS NOT NULL AND Trans2.RevMth <= 3 THEN Trans2.Rev END) as Sum2, 
SUM(CASE WHEN Trans3.Code3 IS NOT NULL AND Trans3.RevMth <= 3 THEN Trans3.Rev END) as Sum3

FROM 
Colleagues LEFT OUTER JOIN
Trans AS Trans1 ON Colleagues.ID = Trans1.code1 LEFT OUTER JOIN
Trans AS Trans2 ON Colleagues.ID = Trans2.code2 LEFT OUTER JOIN
Trans AS Trans3 ON Colleagues.ID = Trans3.code3 

GROUP BY Colleagues.Name

Basically summing up values wherever their codes are found in the table for each code, which I’ve already done using the SUMIFS function on the same data in Excel so I have something to compare to, and normally I could write three separate queries, one for each sum, and then join those in later, but I am trying to do it all in one query.

However the sums that SQL are returning are huge, is there an obvious reason why?

Answer

I doubt you really want a Cartesian product of the transactions. Instead, I think you just want to pick colleagues out of three columns and add up the revenue for which column they are in.

This is easy to do with APPLY and GROUP BY:

SELECT c.Name, SUM(v.rev1), SUM(v.rev2), SUM(v.rev3)
FROM Trans t CROSS APPLY
     (VALUES (t.code1, t.rev, 0, 0), (t.code2, 0, t.rev, 0), (t.code3, 0, 0, t.rev)
     ) v(code, rev1, rev2, rev3) JOIN
     Colleagues c
     ON c.id = v.code
WHERE t.RevMth <= 3
GROUP BY c.Name