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

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

Open in new window

0
techques
Asked:
techques
  • 4
  • 3
1 Solution
 
bmatumburaCommented:

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'

Open in new window

0
 
ralmadaCommented:
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' 

Open in new window

0
 
techquesAuthor 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'

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ralmadaCommented:
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

Open in new window

0
 
ralmadaCommented:
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

Open in new window

0
 
techquesAuthor 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

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'.

Open in new window

0
 
ralmadaCommented:
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
 

Open in new window

0
 
techquesAuthor Commented:
ok, it works now. thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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