Q114253: Sample Code for Summing a Field in a Database

Article: Q114253
Product(s): Microsoft FoxPro
Version(s): MACINTOSH:2.5b; MS-DOS:2.0,2.5,2.5a,2.5b,2.6; WINDOWS:2.5,2.5a,2.5b,2.6,3.0
Operating System(s): 
Keyword(s): kbcode
Last Modified: 09-FEB-2000

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual FoxPro for Windows, version 3.0 
- Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6 
- Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, 2.5a, 2.5b, 2.6 
- Microsoft FoxPro for Macintosh, version 2.5b 
-------------------------------------------------------------------------------

SUMMARY
=======

FoxPro does not provide a function that sums a field in a database. The closest
FoxPro comes is the command SUM <field> TO memvar. Although this command
is useful, you may need to call a function that will sum a field for a given
condition.

The sample code below is a user-defined function (UDF) that can sum a field in
any specified table for any given condition.

MORE INFORMATION
================

     **************************************************************
     *                                                            *
     *  Function:     DBSUM()                                     *
     *  Parameters:                                               *
     *      fieldname   C   Required                              *
     *      workarea    N   Optional                              *
     *      condition   C   Optional                              *
     *                                                            *
     *                                                            *
     *   Purpose: Sums any field in the current or specified      *
     *            work area for any logical condition.            *
     *                                                            *
     *   NOTE: When summing a field that is not in the current    *
     *   work area, the alias must be supplied in the first       *
     *   parameter.                                               *
     **************************************************************

     PARAMETERS fieldname, workarea, condition

     *******************************
     * Store parameter count and current work area to memory variables
     *******************************

     STORE PARAMETERS() TO parms
     STORE SELECT() TO currselect

     *******************************
     * Verify that the field name passed in is a numeric field
     *******************************

     IF TYPE(fieldname) != 'N'
        WAIT WINDOW "Data type mismatch" NOWAIT
        RETURN ""
     ELSE

     *******************************
     * Store current record and total records to memory variables
     * Initialize m.sum memory variable
     *******************************

     currecord = RECNO(IIF(parms>1,workarea,currselect))
     m.sum = 0

     *******************************
     * Select the correct work area if not current work area
     *******************************

     IF parms > 1
        SELECT (workarea)
     ENDIF

     *******************************
     * Position cursor at top of file
     * Begin summation loop
     *******************************

     GO TOP
     SCAN FOR IIF(parms > 2,EVALUATE(condition),.T.)
       m.sum = m.sum + EVALUATE(fieldname)
       SET MESSAGE TO ALLTRIM(STR(m.sum,10,2))
     ENDSCAN

     *******************************
     * Reset record pointer
     ******************************

     DO CASE
        CASE currecord > reccount()
           GO BOTTOM
           SKIP 1
        CASE currecord < reccount()
           GO TOP
           SKIP -1
        OTHERWISE
           GO currecord
     ENDCASE

     *******************************
     * Select the original work area if necessary
     *******************************

     IF parms > 1
        SELECT (currselect)
     ENDIF

     SET MESSAGE TO
     RETURN m.sum
     ENDIF

  To use the DBSUM() function, execute the following commands in the Command
  window or in a program:

     USE customer IN 1
     SELECT 0
     ? dbsum("customer.ytdpurch",1,"state = 'NC'")

Additional query words: VFoxWin FoxMac FoxDos FoxWin total

======================================================================
Keywords          : kbcode 
Technology        : kbHWMAC kbOSMAC kbVFPsearch kbAudDeveloper kbFoxproSearch kbZNotKeyword3 kbFoxPro250bMac kbFoxPro200DOS kbFoxPro250DOS kbFoxPro250aDOS kbFoxPro250bDOS kbFoxPro260DOS kbFoxPro260 kbFoxPro250 kbFoxPro250a kbFoxPro250b kbVFP300
Version           : MACINTOSH:2.5b; MS-DOS:2.0,2.5,2.5a,2.5b,2.6; WINDOWS:2.5,2.5a,2.5b,2.6,3.0

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1986-2002.