Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.

Breck Carter
Last modified: May 16, 1996
mail to:

Counting With CumulativeSum()

"I have a DataWindow that shows students and schools they previously attended so there is sometimes more than one school per student. I'm using Suppress Repeating Values to show each student's name only once. I want to number the students 1, 2, 3 but the DataWindow keeps showing 1, 2, 4. How can this be done?"

Figure 1 shows the desired effect where each new value of student name gets a new number. The DataWindow GetRow() function won't work because the second student uses up row numbers 2 and 3.

Figure 1: Numbering Distinct Values

   1  Fred Jones   Central Tech
   2  Mary Smith   U of T Schools
                   Spar Academy
   3  Ralph Adams  Eastern Conservatory
The DataWindow CumulativeSum() function is very handy for solving various counting problems, much more so than the count() function. To solve this particular problem create a computed field based on the expression in Figure 2.

Figure 2: Counting With CumulativeSum()

   CumulativeSum ( 1 for all distinct student_name )
Give this computed field a name like student_number. Display it on the the DataWindow and add it to the Suppress Repeating Values list to get the report shown in Figure 1.

Breck Carter can be reached by phone at (416) 763-5200 or via email at