TOTAL

TOTAL

Summarize records by key value to a database (.dbf) file

Syntax

      TOTAL ON <expKey> [FIELDS <idField list>]
            TO <xcDatabase>
            [<scope>] [WHILE <lCondition>] [FOR <lCondition>]

Arguments

ON <expKey> defines the group of records that produce a new record in the target database file. To make the summarizing operation accurate, the source database file should be INDEXed or SORTed on this expression.

FIELDS <idField list> specifies the list of numeric fields to TOTAL. If the FIELDS clause is not specified, no numeric fields are totaled. Instead each numeric field in the target file contains the value for the first record matching the key expression.

TO <xcDatabase> is the name of the target file that will contain the copy of the summarized records. Specify this argument as a literal file name or as a character expression enclosed in parentheses. Unless otherwise specified, TOTAL assumes a .dbf extension.

<scope> is the portion of the current database file to TOTAL. The default is ALL records.

WHILE <lCondition> specifies the set of records meeting the condition from the current record until the condition fails.

FOR <lCondition> specifies the conditional set of records to TOTAL within the given scope.

Description

TOTAL is a database command that sequentially processes the current database file, summarizing records by the specified key value and copying them to a new database file. TOTAL works by first copying the structure of the current database file to <xcDatabase>, except for memo fields. It then sequentially scans the current database file within the specified scope of records. As each record with a unique <expKey> value is encountered, that record is copied to the new database file. The values of numeric fields specified in <idField list> from successive records with the same <expKey> value are added to fields with the same names in <xcDatabase>. Summarization proceeds until a record with a new key value is encountered. The process is then repeated for this record.

Since TOTAL processes the source database file sequentially, it must be INDEXed or SORTed in <expKey> order for the summarization to be correct.

To successfully TOTAL numeric fields, the source numeric fields must be large enough to hold the largest total possible for that numeric field. If not, a runtime error is generated.

Notes

. Deleted source records: If DELETED is OFF, deleted records in the source file are TOTALed. Records in the target <xcDatabase> inherit the deleted status of the first matching record in the source file, just as nontotaled fields inherit their values. If DELETED is ON, however, none of the deleted source records are TOTALed.

Examples

      .  In this example, a database file is TOTALed ON the key
         expression of the controlling index using a macro expression.  When
         the macro expression is encountered, the expression is evaluated and
         the resulting character string is substituted for the TOTAL <expKey>
         argument:

      USE Sales INDEX Branch NEW
      TOTAL ON &(INDEXKEY(0)) FIELDS Amount TO Summary

Seealso

AVERAGE, INDEX, SORT SUM

Advertisements

SUM

SUM

Sum numeric expressions and assign results to variables

Syntax

      SUM <nExp list> TO <idVar list>
            [<scope>] [WHILE <lCondition>] [FOR <lCondition>]

Arguments

<nExp list> is the list of numeric values to sum for each record processed.

TO <idVar list> identifies the receiving variables to be assigned assign the results of the sum. Variables that either do not exist or are not visible are created as private variables. <idVar list> must contain the same number of elements as <nExp list>.

<scope> is the portion of the current database file to SUM. The default scope is ALL records.

WHILE <lCondition> specifies the set of records meeting the condition from the current record until the condition fails.

FOR <lCondition> specifies the conditional set of records to SUM within the given scope.

Description

SUM is a database command that totals a series of numeric expressions for a range of records in the current work area and assigns the results to a series of variables. The variables specified in <idVar list> can be field, local, private, public, or static.

Note that the <nExp list> is required and not optional as it is in other dialects.

Examples

      .  This example illustrates the use of SUM:

      LOCAL nTotalPrice, nTotalAmount
      USE Sales NEW
      SUM Price * .10, Amount TO nTotalPrice, nTotalAmount
      //
      ? nTotalPrice               // Result: 151515.00
      ? nTotalAmount              // Result: 150675.00

Seealso

AVERAGE, DBEVAL(), TOTAL

COUNT

COUNT

Tally records to a variable

Syntax

      COUNT TO <idVar>
             [<scope>] [WHILE <lCondition>] [FOR <lCondition>]

Arguments

TO <idVar> identifies the variable that holds the COUNT result. A variable that either does not exist or is invisible is created as a private variable whose scope is the current procedure.

<scope> is the portion of the current database file to COUNT. The default is ALL records.

WHILE <lCondition> specifies the set of records meeting the condition from the current record until the condition fails.

FOR <lCondition> specifies the conditional set of records to COUNT within the given scope.

Description

COUNT tallies the number of records from the current work area that match the specified record scope and conditions. The result is then placed in the specified variable. <idVar> can be a variable of any storage class including a field.

Examples

      .  This example demonstrates a COUNT of Branches in Sales.dbf:
      USE Sales NEW
      COUNT TO nBranchCnt FOR Branch = 100
      ? nBranchCnt                           // Result: 4
      .  This example tallies the number of records in Sales.dbf whose
      Branch has the value of 100 and assigns the result to the Count field
      in Branch.dbf for branch 100:
      USE Branch INDEX Branch NEW
      SEEK 100
      USE Sales INDEX SalesBranch NEW
      SEEK 100
      COUNT TO Branch->Count WHILE Branch = 100

Seealso

AVERAGE, DBEVAL(), SUM, TOTAL

SP_SUM_AVE

SUM_AVE()

  Short:
  ------
  SUM_AVE() Interactive sum or average on a dbf field

  Returns:
  --------
  Nothing

  Syntax:
  -------
  SUM_AVE([cSumAve])

  Description:
  ------------
  Does a SUM or AVERAGE on a selected numeric field

  [csumAve] = "SUM" or "AVE". Default is "SUM"

  Examples:
  ---------
   case nChoice = 3  && sum

     SUM_AVE("SUM")

   case nChoice = 4  && average

     SUM_AVE("AVE")

  Notes:
  -------
  If sls_query() is not empty, an  optional SUM or
  AVERAGE for QUERY can be done.

  Source:
  -------
  S_SUMAV.PRG

 

SP_DBSTATS

DBSTATS()

  Short:
  ------
  DBSTATS() Statistical report on dbf, including
  sum/avg/min/max/std/var/count

  Returns:
  --------
  Nothing

  Syntax:
  -------
  DBSTATS()

  Description:
  ------------
  This is a point & shoot metafunction which allows the
  user to get statistical data on a dbf, particularly with
  numeric fields.

  Statistics available are: count, sum, average,
  minimum, maximum, variance and standard deviation. the analysis
  may also be based on a conditional criteria.

  Examples:
  ---------
   use (cDbfName)

   DBSTATS()    // its a menu driven metafunction

  Source:
  -------
  S_DBSTAT.PRG

 

SP_AMVARIANCE

AMVARIANCE()

  Short:
  ------
  AMVARIANCE() Variance on a given element of multi-dim array

  Returns:
  --------
  <nVariance> => average of array element

  Syntax:
  -------
  AMVARIANCE(aMult,nElem,[bCondition])

  Description:
  ------------
  Returns variance of array <aMult> element <nElem>.
  [bCondition] is an optional codeblock used to select a subset of
  the array. This could be used to filter out 0's or non-numeric
  elements. The block must accept a subarray as a parameter, and
  return  true or false <expL> to determine if this element is
  part of the desired subset. Please note that the codeblock
  accepts the whole subarray, not  just subarray element <nElem>

  Examples:
  ---------

   ?"Total file size here is "
   ??AMSUM(DIRECTORY(),2)

   ?"Total .EXE file size here is "
   ??AMSUM(DIRECTORY(),2,{|e|".EXE"$e[1]}  )

   ?"Variance:"
   ??AMVARIANCE(DIRECTORY(),2,{|e|".EXE"$e[1]}  )

  Notes:
  -------
  Coded by Matthew Maier.

  Presumes all sub-arrays are of equal length

  Source:
  -------
  S_AMSTAT.PRG

 

SP_AMSUM

AMSUM()

  Short:
  ------
  AMSUM() Sum on a given element of multi-dim array

  Returns:
  --------
  <nSum> => sum of array element

  Syntax:
  -------
  AMSUM(aMult,nElem,[bCondition])

  Description:
  ------------
  Returns sum of array <aMult> element <nElem>.

  [bCondition] is an optional codeblock used to select
  a subset of the  array. This could be used to filter out 0's or
  non-numeric elements.  The block must accept a subarray as a
  parameter, and return  true or false <expL> to determine if this
  element is part of the desired subset. Please note that the
  codeblock accepts the whole subarray, not  just subarray element
  <nElem>

  Examples:
  ---------

   ?"Total file size here is "
   ??AMSUM(DIRECTORY(),2)

   ?"Total .EXE file size here is "
   ??AMSUM(DIRECTORY(),2,{|e|".EXE"$e[1]}  )

   use customer
   ?"Total field size "
   ??AMSUM(DBSTRUCT(),3)

   use customer
   ?"Total CHARACTER field size "
   ??AMSUM(DBSTRUCT(),3,{|e|e[2]=="C"} )

  Notes:
  -------
  Coded by Matthew Maier.

  Presumes all sub-arrays are of equal length

  Source:
  -------
  S_AMSTAT.PRG