2 Replies Latest reply: Oct 8, 2012 10:35 AM by Byron Schuurman RSS

    Only multiply when count is lager than 1

      Hello,

       

      I have a fairy simpel problem for which is still don't have a correct solution. A user with a user.id can subscribe to one or more subscriptions. All purchased subscriptions are present in a usersubscription table that has a unique usersubscription id and corresponding user ids (usersubscription.id, user.id). The total revenue is caculated by multiplying the amount of a subscription (subscription.amount) with count(usersubscription.id).

       

      So, I have the following straight table  which is sorted by user.id:

       

       

      usersubscription.id
      user.id                  

      revenue

      (subscription.amount * count(usersubscription.id)



      Total = 31253
      42343824342528
      63463824342634
      55534824342752
      45474824342834
      41246824342834
      86742824342939
      32156824343028
      12567824343052
      86723824343128

       

      Now I only want to calculate the revenue if there are more than 1 user.id, so in this case the user.ids that are bold. I've tried numerous expressions, with e.g. aggregated count, but with no success. I think there is a very simpel solution for this, but I can find it. ;-)
      Can anyone help? Thanks in advance.