Partial SUM of values on a query.

[jrvf]

Hi there,

Imagine we have a table like:


ID ValueA
1 20


2 35
3 23
4 42
5 61
6 32
7 40


And we want to query that table with new column containg the partial sum of the valuesA listed.

Let me give you some examples:

1 - list table where ID are even:


ID ValueA SUM
2 35 35
4 42 77
6 32 109


2 - list table where ID are odd:


ID ValueA SUM
1 20 20
3 23 43
5 61 104
7 40 144


3 - list table where valueA is less than 41:


ID ValueA SUM
1 20 20
2 35 55
3 23 78
6 32 110
7 40 150


Is there anyway we can do that?
Does anyone know the SQL declaration that allow us to do that?

Thank you for all your support.

[ryan1313]

It looks like you want to do a running total on those values. Here is a "how to" from microsoft on setting that up.

http://support.microsoft.com/kb/290136

[weekend00]

if you want one kind at one query, that is simple.
following query is for several sums at one query:

SELECT Sum(IIf((ID Mod 2)=0,ValueA,0)) AS SumEven, Sum(IIf((ID Mod 2)=1,ValueA,0)) AS SumOdd, Sum(IIf(ID<41,ValueA,0)) AS SumLess41
FROM YourTable;