3 Replies Latest reply: Apr 24, 2013 1:34 PM by Greg Frank RSS

    sum(fieldname) after distinct of another field

    Henco van Ee

      Hi,

       

      We are working with contracts. So, a lot of our customers have a contract with a value in euro's. For example, 2.100 euro / 4.200 euro.

      Now, I would like to sum all these contractvalues. The problem is that I get more rows per contract after loading the script. This is correct, because all the ordernumbers have a connection with a contract, so the value of the contract comes as much as the orders which are linked.

       

      What I would like to do is first make a distinct on contract_id and then sum the values of the contracts. It has to be an expression, so I can show it in a table per daterange or salesman.

       

      Is there someone who can help me?

       

      Example:

       

      contract_idordernumbercontractvalue
      12341502100
      12341512100
      12341522100

       

      A sum of the contractvalues in this case is 3 x 2100 = 6300, but it is the same contract.

      So first of all I would like to distinct the contract_id and then sum the values.

       

      Thanks in advance.

       

      Kind regards,

       

      Henco