How to calculate total sum of certain field in an table?

Couple of things to search for in the documentation are: Accumulate, and Accum

A simplified example for both of these together would be:

FOR EACH Sales NO-LOCK WHERE Sales.Sale_Date >= TODAY AND Sales.Sale_Date <= TODAY BREAK BY Sales.Debtor:
  ACCUMULATE Sales.Sale_Value (SUM BY Sales.Debtor).

  IF LAST-OF ( Sales.Debtor ) THEN
  DO:
    DISPLAY Sales.Debtor.
    DISPLAY ACCUM SUM BY Sales.Debtor Sales.Sales_Value.
  END.
END.

Hi,

Thanks for your response.

I have tried this but only for first record it gives the correct value, and also i need to export to CSV file.

There are two fields from same table need to be calculated and export it dont want to display it.

Can you help me without using the Accum function by query itself ?

This is my query which am trying but not fetching the correct data.

Please hep me to sort this out.

OUTPUT TO "C:\Businesswise_Property_Jan2015_test.csv".

EXPORT DELIMITER "," "Policy" "Start Date" "Gross Premium USD" "Gross commission USD" "Transcation date" "Cedco" "Cedant name" "Insured Id" "Insured Name" "Broker code" "Broker Name" "Country" " Exchange Rate" " RI Sum Insured" "RSA Share" "TSI" "Rate%".

FOR EACH uwm100 WHERE COMDAT >= 12/29/2014 AND COMDAT <= 01/28/2015

                                                      AND poltyp begins "c"

            AND uwm100.releas = yes

            NO-LOCK BY uwm100.policy:

Assign n_prem = 0

       n_comm = 0.

Find buwm100 no-lock where buwm100.policy = uwm100.policy and

                              buwm100.rencnt = uwm100.rencnt and

                              buwm100.endcnt = uwm100.endcnt no-error.

If avail buwm100 then

 Assign  

 n_prem = n_prem + buwm100.prem_t

 n_comm = n_comm + buwm100.com1_t.

  EXPORT DELIMITER "," uwm100.policy uwm100.accdat n_prem n_comm uwm100.trndat uwm100.cedco n_cednam uwm100.insref uwm100.name1 uwm100.acno1 n_bacnam n_ctydes uwm100.curate uwm100.sigr_p uwm100.riin_p uwm100.cedsi.

END.

OUTPUT CLOSE.

Hi friends,

Can you help on above?

Hi,

Couple of questions, because I didn't get what you want.

Is buwm100 a buffer for table uwm100? if not what is their relation?

In your algorithm you're not accumulating anything, since n_prem and n_comm are being initialized in each iteration.

There are other things that are wrong but I think it's better if you could give us a little more info.

EDIT!

I see you said you want to check the same table, so I suppose buwm100 is a buffer for uwm100. 

are .policy .renctn and .endcnt part of unique index?

What are the fields of the primary index?

You should only use FIND without FIRST/LAST qualifier only if the where condition matchs with a unique index.

Hi,

Yes buwm100 is a buffer of uwm100.

Policy was a unique index.

Rencnt and endcnt are used to mention the versions of field policy.

i need to sum up the amounts of all versions.

Could you please give me a query?

Ok, but if Policy is the only field of a unique index why did you add two more conditions in the where clause?

Policy being unique makes your FIND only fetch 1 record. What doesn't make sense if you want to accumulate values.

What are the primary and unique index of this table, And the fields that are part of them?