Solved

# How to sum a column in a table?

Posted on 2009-04-17
177 Views
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
Question by:techques

LVL 11

Expert Comment

``````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

LVL 41

Expert Comment

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 Comment

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

LVL 41

Expert Comment

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

LVL 41

Expert Comment

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 Comment

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

LVL 41

Accepted Solution

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 Comment

ok, it works now. thanks
0

## Featured Post

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…