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: June 25, 1996
mail to:

Distinct Sums

How do I compute the sum of invoice amounts in a group trailer without counting the same amount more than once? The detail band can contain multiple payment amounts for one invoice so the same invoice amount appears in more than one detail row.

Here's what the DataWindow select looks like:

   select invoice.customer_id,
     from invoice,
    where ( invoice.invoice_id = payment.invoice_id )

If more than one payment has been made for the same invoice the result set will contain multiple rows for that invoice, and each of those rows will repeat the same invoice amount. You can put invoice_amount in the Suppress Repeating Values list but that doesn't stop it from being double-counted in a computed field expression.

Showing the total of all payment amounts for a customer is a straighforward affair: Define a group on customer_id and put the following computed field in the trailer:

   sum ( payment_amount for group 1 )

Showing the total of all invoice amounts with no double-counting is a different matter. The sum() function must also be told to "suppress repeating values" and include only those invoice amounts from rows that have different values of invoice_id:

   sum ( invoice_amount for group 1 distinct invoice_id )

If an inner group is defined to show the total of all payments made for each invoice, as in

   sum ( payment_amount for group 2 )

the invoice amount can also be displayed in that trailer by simply repeating the invoice_amount column in the trailer. The value is identical for all rows in the group so you don't even need to create a computed field.

Here's what it looks like when it's all put together:

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