cumulative field

Dear all,
Here's a table:
a
----
1
2
3
4
5

i would like to add a column b, and write a program so that the following
result can be drawn:
a b
--- ---
1 1
2 3
3 6
4 10
5 15

you see, the field b is the cumulative sum of a.

How can i perform this by foxpro program, i am using foxpro 8

Leemi
Wed Oct 12 11:01:56 CDT 2005

Hi PatrickHO:

Use the ALTER TABLE command to add a numeric field to the table. Then use
a write a loop (you can use a SCAN...ENDSCAN) to skip through the records
and sum the values of the first field.
Finally, use a REPLACE command to insert the total in the appropriate
record within the loop.

I hope this helps.

This posting is provided "AS IS" with no warranties, and confers no rights.

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- VFP9 HAS ARRIVED!! --*
Read about all the new features of VFP9 here:
http://msdn.microsoft.com/vfoxpro/

*--Purchase VFP 9.0 here:
http://www.microsoft.com/PRODUCTS/info/product.aspx?view=22&pcid=54787e64-52
69-4500-8bf2-3f06689f4ab3&type=ovr

Keep an eye on the product lifecycle for Visual FoxPro here:
http://support.microsoft.com/gp/lifeselectindex
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003



>Dear all,
>Here's a table:
>a
>----
>1
>2
>3
>4
>5

>i would like to add a column b, and write a program so that the following
>result can be drawn:
>a b
>--- ---
>1 1
>2 3
>3 6
>4 10
>5 15

>you see, the field b is the cumulative sum of a.

>How can i perform this by foxpro program, i am using foxpro 8 >

Anders
Wed Oct 12 20:23:37 CDT 2005


SELECT T1.num, SUM(T2.num) FROM xx as T1 JOIN xx AS T2 ;
ON T1.num >=T2.num GROUP BY T1.num

or
CREATE CURSOR xx (num Int, sum_num Int)
SELECT Table
lnSum = 0
SCAN
lnNum=table.num
lnSum=lnSum+lcNum
? lnNum, lnSum
INSERT INTO xx VALUES (lnNum, lnSum)
ENDSCAN

-Anders

"PatrickHO@ASC" <patrick_asc@yahoo.com.hk> skrev i meddelandet
news:%23QYZ2wzzFHA.404@TK2MSFTNGP09.phx.gbl...
> Dear all,
> Here's a table:
> a
> ----
> 1
> 2
> 3
> 4
> 5
>
> i would like to add a column b, and write a program so that the following
> result can be drawn:
> a b
> --- ---
> 1 1
> 2 3
> 3 6
> 4 10
> 5 15
>
> you see, the field b is the cumulative sum of a.
>
> How can i perform this by foxpro program, i am using foxpro 8
>

Anders
Wed Oct 12 20:50:20 CDT 2005

There will be a problem with the SQLECT query if there are two identical
values, as the GROUP BY clause will merge them, or if the values aren't in
ascending order, as the GROUP BY clause will re-order them.
If your table has a unique key column in ascending order it can by used to
separate identical values:

ALTER TABLE Table ADD COLUMN id Int
UPDATE Table SET id = RECN()

SELECT T1.id,T1.num, SUM(T2.num)AS sum_num ;
FROM Table as T1 LEFT JOIN Table AS T2 ;
ON T1.id>=T2.id GROUP BY T1.id, T1.num;
ORDER BY T1.id

-Anders

"PatrickHO@ASC" <patrick_asc@yahoo.com.hk> skrev i meddelandet
news:%23QYZ2wzzFHA.404@TK2MSFTNGP09.phx.gbl...
> Dear all,
> Here's a table:
> a
> ----
> 1
> 2
> 3
> 4
> 5
>
> i would like to add a column b, and write a program so that the following
> result can be drawn:
> a b
> --- ---
> 1 1
> 2 3
> 3 6
> 4 10
> 5 15
>
> you see, the field b is the cumulative sum of a.
>
> How can i perform this by foxpro program, i am using foxpro 8
>

Rush
Tue Oct 25 20:54:27 CDT 2005

Don't do it. Instead, read about "normalization." And then build a view
(as Anders suggested) to generate that data on the fly.

- Rush

"PatrickHO@ASC" <patrick_asc@yahoo.com.hk> wrote in message
news:%23QYZ2wzzFHA.404@TK2MSFTNGP09.phx.gbl...
> Dear all,
> Here's a table:
> a
> ----
> 1
> 2
> 3
> 4
> 5
>
> i would like to add a column b, and write a program so that the following
> result can be drawn:
> a b
> --- ---
> 1 1
> 2 3
> 3 6
> 4 10
> 5 15
>
> you see, the field b is the cumulative sum of a.
>
> How can i perform this by foxpro program, i am using foxpro 8
>