• Status: Solved
• Priority: Medium
• Security: Public
• Views: 226

# How to sum a column in a table?

Here are the data of 2 tables:
account table:
[id]
,[shortcode]
,[currencyid]
,[currency]
,[type]
,[balance]

1      TST      0      HKD      saving      10000.000000
2      TST      0      RMD      saving      10000.000000
3      TST      0      HKD      saving      10000.000000
4      TST      0      RMD      saving      10000.000000
5      TST      0      USD      saving      10000.000000
6      TST      0      EUR      saving      10000.000000
7      TST      0      JPY      saving      10000.000000
8      TST      0      GBP      saving      10000.000000
9      TST      0      KRW      saving      10000.000000
10      TST      0      AUD      saving      10000.000000

subtransaction table:
[id]
,[accountid]
,[amount]
,[payoutaccountid]
,[payoutamount]
,[subdatetime]
25      2      300.000000          1      333.330000
26      4      50.000000          3      53.000000
27      2      100.000000          1      110.000000
28      21      50.000000          301      55.000000
29      301      800.000000          92      800.000000

How can I get the result of a.balance + sum(s.amount) - sum(s.payoutamount) properly?
``````select (a.balance + sum(s.amount) - sum(s.payoutamount)) balance
from account a inner join subtransaction s
on s.accountid = a.id and s.payoutaccountid = a.id
where year(s.subdatetime) = '2009' and month(s.subdatetime) = '04'
group by a.balance
``````
0
techques
• 4
• 3
1 Solution

Commented:

``````SELECT a.balance + (SELECT SUM(amount) FROM subtransactiontable WHERE accountid = a.id) - (SELECT SUM(payoutamount) FROM subtransactiontable WHERE accountid = a.id) AS balance FROM account AS a INNER JOIN subtransaction AS s ON a.id = s.accountid AND year(s.subdatetime) = '2009' and month(s.subdatetime) = '04'
``````
0

Commented:
Don't need to subqueries.
try this:

``````SELECT a.balance +
(SELECT SUM(amount - payoutamount) FROM subtransactiontable WHERE accountid = a.id) AS balance

FROM account AS a
INNER JOIN subtransaction AS s ON a.id = s.accountid AND year(s.subdatetime) = '2009' and month(s.subdatetime) = '04'
``````
0

Author Commented:
I changed the query a little.

1. SELECT SUM(payoutamount) FROM subtransaction WHERE accountid = a.id
to
SELECT SUM(payoutamount) FROM subtransaction WHERE payoutaccountid = a.id

because a.balance - payoutaccountid's sum(payoutamount)

2. the result

2      NULL
4      NULL
2      NULL
21      NULL
301      10745.000000

however, accountid, amount has
2      300.000000
4      50.000000
2      100.000000
21      50.000000
301      800.000000

payoutaccountid, payoutamount has
1      333.330000
3      53.000000
1      110.000000
301      55.000000
92      800.000000

so, 1, 3, 92 are missed

3. For other accountid which do not have subtransaction data, their initial balance is 10000 and I need to show all accountid's balance including those without subtransaction

But, it is limited by year(s.subdatetime) = '2009' and month(s.subdatetime) = '04'

How can I fix it?

``````SELECT a.id, a.balance
+ (SELECT SUM(amount) FROM subtransaction WHERE accountid = a.id)
- (SELECT SUM(payoutamount) FROM subtransaction WHERE payoutaccountid = a.id) AS balance
FROM account AS a
INNER JOIN subtransaction AS s
ON a.id = s.accountid
and year(s.subdatetime) = '2009' and month(s.subdatetime) = '04'
``````
0

Commented:
Give this a try:
``````SELECT 	a.id,
(a.balance + s.amount - d.payoutamount) as balance
FROM account a
LEFT JOIN (select accountid, sum(amount) as amount
from subtransaction
where year(subdatetime) = '2009' and month(subdatetime) = '04'
group by accountid
) s ON a.id = s.accountid
LEFT JOIN (select payoutaccountid, sum(payoutamount) as payoutamount
from subtransaction
where year(subdatetime) = '2009' and month(subdatetime) = '04'
group by payoutaccountid
) d ON a.id = d.payoutaccountid
``````
0

Commented:
Actually one little correction:
``````SELECT 	a.id,
(a.balance + isnull(s.amount, 0) - isnull(d.payoutamount, 0)) as balance
FROM account a
LEFT JOIN (select accountid, sum(amount) as amount
from subtransaction
where year(subdatetime) = '2009' and month(subdatetime) = '04'
group by accountid
) s ON a.id = s.accountid
LEFT JOIN (select payoutaccountid, sum(payoutamount) as payoutamount
from subtransaction
where year(subdatetime) = '2009' and month(subdatetime) = '04'
group by payoutaccountid
) d ON a.id = d.payoutaccountid
``````
0

Author Commented:
Dear Sir

Thanks for help. It can generate the desired result.

1      9556.670000
2      10400.000000
3      9947.000000
4      10050.000000
5      10000.000000
6      10000.000000
7      10000.000000
8      10000.000000
9      10000.000000
10      10000.000000

I changed it to be a function and call it select dbo.ufn_Cal_Daily_Asset('2009','04') asset, but why the return become

10000.000000

But not the list of all data?

And, if I need to sum of all result, how should I write it?

Finally, each row of data needs to multipy an exchange rate.

select referencerate from exchanges where currency = (select currency from account where id=subtransaction.accountid)

I edit the code but one accountid show null value

select accountid, (sum(amount) * (select referencerate from exchanges where currency = (select currency from account where id=accountid))) as amount
from subtransaction
where year(subdatetime) = '2009' and month(subdatetime) = '04'
group by accountid

result without referencerate
2      400.000000
4      50.000000
21      50.000000
301      800.000000

2      448.000000
4      56.000000
21      NULL
301      800.000000

Schema of exchages
SELECT [id]
,[currency]
,[referencerate]
,[systemdate]
FROM exchanges

I will add more point to this question. Thanks for your kind help.

``````ALTER FUNCTION [dbo].[ufn_Cal_Daily_Asset]
(
@year nvarchar (50),
@month nvarchar(50)
)
Returns decimal(18, 6)
AS
BEGIN
Declare @return decimal(18,6)
select @return = 0

SELECT
@return =
(a.balance + isnull(s.amount, 0) - isnull(d.payoutamount, 0)) --as balance
FROM account a
LEFT JOIN (select accountid, sum(amount) as amount
from subtransaction
where year(subdatetime) = @year and month(subdatetime) = @month
group by accountid
) s ON a.id = s.accountid
LEFT JOIN (select payoutaccountid, sum(payoutamount) as payoutamount
from subtransaction
where year(subdatetime) = @year and month(subdatetime) = @month
group by payoutaccountid
) d ON a.id = d.payoutaccountid

RETURN @return
END

//how to write sum? I got error after adding sum()

SELECT
@return =
sum ( (a.balance + isnull(s.amount, 0) - isnull(d.payoutamount, 0)) --as balance
FROM account a
LEFT JOIN (select accountid, sum(amount) as amount
from subtransaction
where year(subdatetime) = @year and month(subdatetime) = @month
group by accountid
) s ON a.id = s.accountid
LEFT JOIN (select payoutaccountid, sum(payoutamount) as payoutamount
from subtransaction
where year(subdatetime) = @year and month(subdatetime) = @month
group by payoutaccountid
) d ON a.id = d.payoutaccountid
)

Msg 156, Level 15, State 1, Procedure ufn_Cal_Daily_Asset, Line 20
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Procedure ufn_Cal_Daily_Asset, Line 25
Incorrect syntax near 's'.
Msg 102, Level 15, State 1, Procedure ufn_Cal_Daily_Asset, Line 30
Incorrect syntax near 'd'.
``````
0

Commented:
Instead of a function, use a stored procedure. And call it like this
exec Cal_Daily_Asset @year = 2009, @month = 4

The attached code will also include the exchange rate calculation as well. Will appreciate maximum points ;-)

``````CREATE PROCEDURE Cal_Daily_Asset
@year int,
@month int
AS
BEGIN

SELECT a.accountid,
sum((a.balance + isnull(s.amount, 0) - isnull(d.payoutamount, 0)) * ex.referencerate) as balance
FROM account a
LEFT JOIN (select accountid, sum(amount) as amount
from subtransaction
where year(subdatetime) = @year and month(subdatetime) = @month
group by accountid
) s ON a.id = s.accountid
LEFT JOIN (select payoutaccountid, sum(payoutamount) as payoutamount
from subtransaction
where year(subdatetime) = @year and month(subdatetime) = @month
group by payoutaccountid
) d ON a.id = d.payoutaccountid
INNER JOIN exchanges ex ON ex.currency = a.currency
group by a.accountid with cube
END

``````
0

Author Commented:
ok, it works now. thanks
0

## Featured Post

• 4
• 3
Tackle projects and never again get stuck behind a technical roadblock.