MS-SQL: Add the value of a column in a generated column

Question

0.00/5 (No votes)

See more:

Hi,

I have a table which hold a ID and a Value.

ID    Value
2     100
5     250
8     150
12    200

Now I want a query which orders the Value column descending.

SQL

SELECT ID, Value 
FROM Values 
ORDER BY Value DESC

But it has to have another column(Total) where the value is added with each record, so the result will be this:

ID    Value   Total
5     250     250
12    200     400 (250 + 200)
8     150     550 (400 + 150)
2     100     650 (550 + 100)

How can I achive this?

Posted 9-May-12 5:09am

willempipi

Add a Solution

4 solutions

Solution 1

try here? Calculating simple running totals in SQL Server[^]

Posted 9-May-12 5:21am

scottgp

Comments

Wendelius 9-May-12 13:58pm

Hopefully that helps :)

Solution 2

The specific query in this case could be for example something like:

SQL

SELECT A.ID, 
       A.Value 
       (SELECT SUM(Value)
        FROM   Values B
        WHERE  B.Id <= A.Id) AS Total
FROM Values A
ORDER BY A.Value DESC

Posted 9-May-12 7:55am

Wendelius

Comments

willempipi 14-May-12 9:57am

I'm sorry, this is not the solution, since a higher ID is not automaticly a higher Value. Ended up making a C# program that simply counts the values because I hate SQL Cursors...

Wendelius 14-May-12 14:18pm

Yes, the Id was used only as an example. In real situation yo umust use the column or columns that really define the order you want to use.

Solution 4

see the below code..it will work

SQL

select a1.id,a1.value, sum(a2.value)
from Values a1, Values a2
where a1.id < a2.id or (a1.id = a2.id and a1.value=a2.value)
group by a1.id,a1.value
order by a1.id desc ,a1.value desc

Posted 14-May-12 18:26pm

ssd_coolguy

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

willempipi · Accepted Answer · 2012-05-14T04:00:00

Solution 3

Use a cursor or write a C# app that counts the values, there's no SELECT statement for this.

Posted 14-May-12 4:00am

willempipi