2 Replies Latest reply: Jul 10, 2016 3:22 PM by Ankit Tripathi RSS

    Aggregate function in set logic

    Ankit Tr

      Hi,

       

      I have following data sample:

      Table 1

      Order id        item id               price

      1                    x                       20

      1                    y                       30

      1                    z                       40

       

       

      Item id        Owner id               Name

      x                     123                    John

      y                     124                   Harrry

      z                     125                    Neo

       

       

       

      I need to count number of order ids where price given by specific owner was minimum, maximum or 2nd (1 order id can have only 3 item ids)

       

      I have tried the following query:

      count(distinct {$< "aggr(firstsortedvalue(Price),-[Item ID]),[Order ID])"

      ={"$aggr(min(Price),[Trip ID])"}

      >} [Trip ID])

       

      My intention is when I filter out a certain name(say Neo), I would get:

       

      Agent  Name             #Order Ids where price is lowest                #Order Ids where price is 2nd    #Order Ids where price is max

       

      Neo                                       some number                                     some number                               some number

       

      Thanks for your help

        • Re: Aggregate function in set logic
          Sunny Talwar

          Would you be able to provide some additional sample? I am not entirely sure as to what is needed. May be provide actual numbers instead of some number for the expected output as well.

           

          Best,

          Sunny

            • Re: Aggregate function in set logic
              Ankit Tripathi

              Hi Sunny,

               

              This might be more clear:

               

              Order id        item id               price

              1                    x                       20

              1                    y                       30

              1                    z                       40

              2                    a                       10

              2                    b                       5

              2                    c                       50

               

               

              Item id        Owner id               Name

              x                     123                    John

              y                     124                    Harrry

              z                     125                    Neo

              a                     127                    Neo

              b                     129                    Rocky

              c                      130                   Rohan

               

              Agent  Name        #Order Ids where price is lowest       #Order Ids where price is 2nd    #Order Ids where price is max

               

              Neo                                           0                                                           1                                              1

               

              Every order id will have 3 or more items, each item has ann owner.
              Problem is to find the number of order ids where specific owners's quoted item was 1st , 2nd and third.

               

              Thanks .