CODEWITHSUNDEEP
sqlvisual-foxprofoxpro
DizzleBeans
DizzleBeans

Reputation: 193

FoxPro Conditional Sum Statement

I am trying to perform a conditional sum on a column of data in a FoxPro program. I think it should be able to be done in one line or so. So far I have something that looks like:

public sumvar
sumvar = SUM(TABLE2.val WHERE table2.id = table1.id)

This is being done inside a loop, so for this instance, table1.id might equal 4, and there are say 3 rows in table2 which have table2.id = 4. So I want to sum the table2.val value for just these 3 rows together. Any help much appreciated!

Upvotes: 0

Views: 228

Answers (1)

Stefan Wuebbe
Stefan Wuebbe

Reputation: 2149

I'd recommend the SQL way rather than the xBase Sum command

CLEAR

CREATE CURSOR test (id Int, amount N(15,2))
INSERT INTO test VALUES (1, 15.45)
INSERT INTO test VALUES (1, 8)
INSERT INTO test VALUES (2, 9)
GO TOP IN test

* SQL
LOCAL ARRAY laSum[1]
SELECT SUM(amount) FROM test WHERE id = 1 INTO ARRAY laSum
? laSum[1]

* xBase
SUM amount FOR id = 1 TO result
? m.result
* F1 Help says: "Totals all or specified numeric fields in the **currently selected table**"

Another disadvantage of the xBase command would be moving record pointers.

Upvotes: 2

Related Questions