'Sum by group in foxpro

I'm newbie in foxpro programming. i have some issue how to fill "sum value" column with value data from second column by id_doc from first column. Thankyou

From this table 1

Table1
Id_doc   Value   Sum_Value
AA       12      ??
AA       11      ??
AA       14      ??
BB       9       ?? 
BB       8       ??
CC       7       ??
CC       10      ??
CC       13      ??

To This Table Table results

I have try this

  SELECT id_doc, SUM(NILAI) as Jml; 
  FROM table2;
  GROUP BY Id_doc;
  
  INNER JOIN Jml_nilai as Jml ;
  ON Id_doc = Id_doc;
  ORDER BY Id_doc ASCENDING;

But it's not running well....

sumfoxpro


Solution 1:[1]

Your current description looks as if you want this:

CREATE CURSOR table1 (id_doc Char(20), value Int, sum_value Int)
INSERT INTO table1 (id_doc, value) VALUES ('AA', 12)
INSERT INTO table1 (id_doc, value) VALUES ('AA', 11)
INSERT INTO table1 (id_doc, value) VALUES ('AA', 14)
INSERT INTO table1 (id_doc, value) VALUES ('BB', 9)
INSERT INTO table1 (id_doc, value) VALUES ('BB', 8)
INSERT INTO table1 (id_doc, value) VALUES ('BB', 7)
INSERT INTO table1 (id_doc, value) VALUES ('CC', 7)
INSERT INTO table1 (id_doc, value) VALUES ('CC', 10)
INSERT INTO table1 (id_doc, value) VALUES ('CC', 13)

SELECT id_doc, SUM(value) FROM table1 GROUP BY id_doc

Edited: This could be the "running group sums self-Join" SQL as mentioned in the currently last comment:

SELECT T1.id_doc, T1.value, T2.grpSum FROM table1 T1 ;
    JOIN (SELECT id_doc, SUM(value) as grpSum FROM table1 GROUP BY id_doc) T2 ;
        On T2.id_doc = T1.id_doc

Later: learned that it's rather called [groupwise-maximum] https://stackoverflow.com/questions/tagged/groupwise-maximum

Solution 2:[2]

Thank you so much for all response. Finally it's done....

Sharing for all. This is what i did.

CLOSE all
SET SAFETY OFF 

******************************************************************


USE table1.dbf 
INDEX on id_doc TO a
TOTAL ON id_doc TO table1_jumlah.dbf

CLOSE ALL 
*************************************************************

sele 1
* data primer (master)
    use table1_jumlah.dbf
    index on id_doc to i


sele 2
* data sekunder (diisi)
    use table1.dbf
    index on id_doc to ii


sele 1
go top
do while .not. eof()
    iddocx=Id_doc
    valuex=Value
    
    sele 2
      loca for id_doc=iddocx
      if found()
        repl ALL Sum_Value with Valuex for id_doc=iddocx 
    endi

   sele 1
   skip
   loop
endd

**********************************************************

CLOSE ALL
DELETE FILE *.bak
DELETE FILE *.idx

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 John Smith

Related Questions