Need to make some values negative in sum computation

Question

I have the following table that has a list of transactions

ID  FromID   ToId      AMOUNT
1   1        2         10
2   1        3         10
4   1        4         10
5   3        2         3
6   4        2         2
7   4        2         1
8   2        4         2

I'd like to know the best/most effecient way to get a users balance. The balance would be the sum of the amount the user has been given (where ID is ToId) minus the sum of the amount the user has given out (where ID is FromId)

Any ideas?

Thanks in advance.

James

In my humble opinion, I'd break that into 2 tables, as your current table seems to represent 2 different things. — Sam I am says Reinstate Monica
– Sam I am says Reinstate Monica, Commented Jul 19, 2012 at 14:33
Hi Sam I am, I open to suggestions but each row in the database represents a transcation from one user to another. Should that be split into 2 tables? — James Privett
– James Privett, Commented Jul 19, 2012 at 14:35

podiluska · Accepted Answer · 2012-07-19 14:35:15Z

6

With a simple sum

select SUM(Amount) from
(
select Amount from #t where ToID=@user
union all
select -Amount from #t where FromID=@user
) v

(You can do it in one line...

select sum(case @user when ToID then Amount else -Amount end) 
from #t where @user in (ToID, FromID)

but I think the union would be more efficient)

answered Jul 19, 2012 at 14:35

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Howansky · Accepted Answer · 2012-07-19 14:54:13Z

1

The "best" way is subjective. The most efficient way probably would be to add a balance field to your user table, and then update that appropriately whenever you have a transaction for that user.

[Edit] To clarify:

In your code that inserts the transaction row, add an update query, inside a transaction:

BEGIN TRANSACTION;
INSERT INTO transactions VALUES (...);
UPDATE users SET balance = balance + <whatever the change is>;
COMMIT TRANSACTION;

Then your balance check simply becomes:

SELECT balance FROM users WHERE id = '...';

It's indexed, extremely fast, and only ever requires one row to be considered. If you rely on an active summation of the transaction table, then your balance check queries will grow slower as you add transactions.

edited Jul 19, 2012 at 14:54

answered Jul 19, 2012 at 14:36

Alex Howansky

54.1k8 gold badges85 silver badges102 bronze badges

8 Comments

Esoteric Screen Name Over a year ago

Keeping a static balance column and manually updating it would be more efficient than a computed column?

Alex Howansky Over a year ago

Manually updating it? Good lord, no. Update it in a SQL transaction whenever you insert a transaction row. It makes the balance check query much more performant than computing the sum of a potentially huge number of rows.

Alex Howansky Over a year ago

@Bazzz: Like I said, "best" is subjective. If you want a perfectly compliant and normalized database, then you have to pay the cost of a potentially expensive query to do something as mundane as a balance check. The decision of which to go with requires much more data about the OP's environment than we have availble.

Esoteric Screen Name Over a year ago

@AlexHowansky - That's what I meant by "manual": user maintained via some piece of code, as opposed to automatically calculated by the server. Is SQL Server not intelligent enough to determine that's the route to take when updating a persisted computed column? That is, will it recompute using all the rows when a new transaction is inserted?

Alex Howansky Over a year ago

@Esoteric: It will do whatever you tell it to, depends on what query you give it. You can have it recalculate from all the rows (I don't see why you'd want to though) or just adjust the existing balance based on the one most recent change.

|

ali haider · Accepted Answer · 2012-07-19 14:36:17Z

0

SUM the two sets of amounts (SUM all amounts given to the user as well as SUM all amounts given out) - subtract the two (if using SQL Server, you can use (-) SUBTRACT)

answered Jul 19, 2012 at 14:36

ali haider

20.6k22 gold badges88 silver badges159 bronze badges

Collectives™ on Stack Overflow

Need to make some values negative in sum computation

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related