8 Replies Latest reply on Jul 25, 2018 6:20 AM by Steve Martin

    "Group By clause of SQL" while creating aggregated calculated field

    Nitin Agrawal

      Is there any way to use "Group By clause of SQL" while creating aggregated calculated field ?


      Let me simplify the scenario:


      There is a table which contains 3 columns 'Merchant ID', Sales(from region A), Sales(from region A). Also the values under the merchant ID can be repeated multiple times(there can be a merchant "X" which is having multiple values for sales)


      Now i want to create a calculated field which aggregates(sum) the sales from both the columns, however it should be aggregated on the basis of a particular merchant. Of course this can be done if "Merchant ID" is added in row, and then aggregates are added, it will show me sum of sales for all merchants.

      However i want the output in number of times in which aggregate sales from region A > region B, region b > region A or region A = region B like:



      Sales                           Number of Merchants

      Region A > Region B                    10

      Region B < Region A                    20

      Region A = Region B                     3



      In short I want a formula or method to satisfy below condition:



      IF SUM([Sales A]) > SUM([Sales B]) THEN "Region A > Region B"

      ELSEIF SUM([Sales A]) < SUM([Sales B]) THEN "Region B < Region A"

      ELSE "Region A = Region B" END


      group by merchant ID;  ---this i want to achieve