techques
asked on
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?
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
Don't need to subqueries.
try this:
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'
ASKER
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?
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'
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
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
ASKER
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('2 009','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.accounti d)
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
result after adding referencerate
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.
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('2
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.accounti
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
result after adding referencerate
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'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, it works now. thanks
Open in new window