CODEWITHSUNDEEP
sql
DOTNET Rocker
DOTNET Rocker

Reputation: 37

SQL query to find sum of a column

I have 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 columns: 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.

Upvotes: 0

Views: 112

Answers (4)

user6333456
user6333456

Reputation: 

select id, amount, sum(amount) as totalAmount from table_name order by id;

Upvotes: 0

Praveen ND
Praveen ND

Reputation: 560

In SQL SERVER 2012 this Query is working fine. Try This:

SELECT 
   ID, 
   AMOUNT, 
   SUM(AMOUNT) 
   OVER(ORDER BY ID) AS TotalAmount
FROM 
   YourTable;

Upvotes: 0

user330315
user330315

Reputation:

You didn't state your DBMS, so this is ANSI SQL:

select id, amount, 
       sum(amount) over (order by id) as totalamount
from the_table

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.id, t.amount,
       (select sum(t2.amount) from t t2 where t2.id <= t.id) as TotalAmount
from t;

Upvotes: 3

Related Questions