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.

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,
          invoice.invoice_id,
          invoice.amount,
          payment.payment_date,
          payment.amount
     from invoice,
          payment
    where ( invoice.invoice_id = payment.invoice_id )

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: