This seems like a basic problem but I can't figure out how to resolve it.
I have a query :
SELECT PR.WBS2, SUM(LedgerAR.Amount * - 1) AS Expr5, LB.AmtBud AS budget
FROM PR LEFT OUTER JOIN
LedgerAR ON PR.WBS1 = LedgerAR.WBS1 AND PR.WBS2 = LedgerAR.WBS2 AND LedgerAR.WBS3 = PR.WBS3 LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND LB.WBS2 = PR.WBS2 AND PR.WBS3 = LB.WBS3
WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> 'zzz') AND (PR.WBS2 <> '98') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (PR.WBS1 = '001-298')
GROUP BY PR.WBS2, LB.AmtBud
ORDER BY PR.WBS2
I want to sum up the middle column and last column grouping by wbs2. However, when I do SUM(lb.amtbud) the budget column is not summing correctly it is summing the column as if the data appeared like this:
Oh no! I just realized what if there are more than one amounts that are duplicates. I have to group this query by wbs2 so does any one have any ideas how to do this? I don't want to use a subquery but I can use a case statement.