2 Replies Latest reply: Jan 22, 2018 7:03 PM by Anil Samineni RSS

    GROUP BY ERROR

    Giuseppe Ceravolo

      Hello,

       

      Can anybody help me on the error below?

       

      Aggregation expressions required by GROUP BY clause.

       

      Operational_Report:

      LOAD

           'US' AS 'Region',

           [Delivery Number],

           [Customer Order],

           [Brand],

           [Items Number],

           [Packages Number],

           Date([Order Transmission Date]) AS [Data arrivo ordine],

           Date([Order Shipping Date]) AS [Data evasione ordine],

           [Actual Weight],

           [Billed Weight],

           [Billed cost],

           [Ship Via Method],

           [Customer Name],

           [Customer Last Name],

           [Customer Name] & [Customer Last Name] AS [Customer Full Name],

           [Destination town],

           [Ship To State],

           [Ship To Zip]

      FROM [lib://Reportistica/@US ME - Operational Report/*.xls]

      (biff, embedded labels, table is [Operational Report$])

      GROUP BY [Delivery Number];

       

      I would like to group this collection of Excel files (Operational Reports) by the Delivery Number.

      The fact is that one Delivery Number may be present in more than 1 row if that Delivery has been shipped with 2 or more Packages.

      For example:

             

      Delivery NumberNumber of ItemsNumber of PackagesOrder Transmission DateOrder Shipping DateActual WeightBilled WeightBilled cost
      218388422/21/20172/21/201715.4825.67
      218388422/21/20172/21/201715.4825.67

       

      Delivery Number 218388 is reported twice because it consisted of 2 packages.

      However, I want to gather data by Delivery Number (218388) and keep 2 as Number of Packages, 4 as Number of Items, 15.4 as Actual Weight, 8 as Billed Weight, and 25.67 as Billed cost.

      Practically speaking, I want to consider only 1 row per each Delivery Number.

       

      Any suggestions on the matter will be highly appreciated.

       

      Thank you,

      Giuseppe