user3732556 user3732556 - 2 years ago 63
SQL Question

Sql Query To Find Sum of A Column

I've a table where I have to get the output as follows:

ID - amount - TotalAmount
1 - 400 - 400
2 - 600 - 1000
3 - 400 - 1400

The table has two columms: ID & amount. The 'TotalAmount' should be created when the sql script runs and hope, the rest of the sum is cleared from the above.

How can I do the above sum?? Please share ideas to do so. Thanks.

Answer Source

This is a cumulative sum. The ANSI standard method is as follows:

select id, amount, sum(amount) over (order by id) as TotalAmount
from t;

Most, but not all databases, support this syntax.

The above is the "right" solution. If your database doesn't support it, then a correlated subquery is one method:

select, t.amount,
       (select sum(t2.amount) from t t2 where <= as TotalAmount
from t;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download