Summing an amount, across two tables.
I have two tables, the first has original amount of a fund, the second is related and has increments to that fund amount. I want to sum the original amount and all the increments. How can I do this?
These two queries return the original amount of a fund, and the sum of the increments.
select amount from tds_fund where id = 4
select sum(amount) from tds_fund_amount_added where fk_fund = 4
These two queries return the original amount of a fund, and the sum of the increments.
select amount from tds_fund where id = 4
select sum(amount) from tds_fund_amount_added where fk_fund = 4
If you need you can add the filter in the main SELECT:
WITH CTE_SUM
AS (
SELECT fk_fund, sum(amount) Total
FROM tds_fund_amount_added
GROUP BY fk_fund
)
SELECT f.id, f.amount+s.Total SumInc
FROM tds_fund f
INNER JOIN CTE_SUM s ON f.id = s.fk_fund
WHERE f.id = 4
SELECT F.id, F.amount AS OriginalAmount, SUM(amount) AS AmountAdded
FROM tds_fund AS F
LEFT OUTER JOIN tds_fund_amount_added AS FAA
ON F.id = FAA.fk_fund
GROUP BY F.id, F.amount
FROM tds_fund AS F
LEFT OUTER JOIN tds_fund_amount_added AS FAA
ON F.id = FAA.fk_fund
GROUP BY F.id, F.amount
ASKER
if I add the where clause, I get a syntax error.
if I add the where clause, I get a syntax error.Please share how are you doing it.
ASKER
in a studio manager window; I get this error:
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
use tdsdev
WITH CTE_SUM
AS (
SELECT fk_fund, sum(amount) Total
FROM tds_fund_amount_added
GROUP BY fk_fund
)
SELECT f.id, f.amount+s.Total SumInc
FROM tds_fund f
INNER JOIN CTE_SUM s ON f.id = s.fk_fund
WHERE f.id = 4
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
use tdsdev
WITH CTE_SUM
AS (
SELECT fk_fund, sum(amount) Total
FROM tds_fund_amount_added
GROUP BY fk_fund
)
SELECT f.id, f.amount+s.Total SumInc
FROM tds_fund f
INNER JOIN CTE_SUM s ON f.id = s.fk_fund
WHERE f.id = 4
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah, got it!. Works great. thanks.
ASKER
I need one more thing - it has to work when there are no records in the related table for a Fund. In this case, I need just the amount in the Fund table.
Use a LEFT OUTER JOIN instead of an INNER JOIN
Use a LEFT JOIN and ISNULL function to replace Null for Zero:
;WITH CTE_SUM
AS (
SELECT fk_fund, sum(amount) Total
FROM tds_fund_amount_added
GROUP BY fk_fund
)
SELECT f.id, f.amount+ISNULL(s.Total,0) SumInc
FROM tds_fund f
LEFT JOIN CTE_SUM s ON f.id = s.fk_fundASKER
ah, yes. DUH. But I also need to do this:
CASE WHEN s.Total IS NULL THEN 0 ELSE s.Total END
CASE WHEN s.Total IS NULL THEN 0 ELSE s.Total END
ASKER
Or ISNULL(s.Total,0).
Thanks again!
Thanks again!
Open in new window