How to use Group By Clause

[jim_chan]

Hi guys,

i wanted to use Group By Clause. my script is as below. It didnt load succerssfully. error is "Aggregation expressions required by GROUP BY clause".

What have been missing in this table?

Total_Sales_Year:

load

EmployeeID,

Year

Resident Orders

Group BY EmployeeID,Year;

Rgds,

Jim

[sunny_talwar]

What is your aggregation here?

Total_Sales_Year:

LOAD EmployeeID,

    Year,

    Sum(Sales)

Resident Orders

Group BY EmployeeID, Year;

[jim_chan]

ooooh.   , i need to create an anggregation when i use group by clause???

[sunny_talwar]

Yes, why else would you want to use a Group by statement?

[jim_chan]

Now i got a synthetic key after loaded.

Can i create a composite key? and add it on top ??

Rgds

Jim

[sunny_talwar]

‌May be left join this into Orders table or combine EmployeeId and Year to create a concatenated key

[jim_chan]

concatenate will duplicate the records.

[sunny_talwar · Accepted solution]

Option 1:

Orders:

LOAD EmployeeID,

          Year,

          Sales,

          ....

FROM Orders;

Left Join (Orders)

LOAD EmployeeID,

    Year,

    Sum(Sales)

Resident Orders

Group By EmployeeID, Year;

Option 2:

Orders:

LOAD EmployeeID,

          Year,

          AutoNumber(EmployeeID&Year, 'Total_Sales_Year') as Key,

          Sales,

          ....

FROM Orders;

Total_Sales_Year:

LOAD Key,

   Sum(Sales)

Resident Orders

Group By Key;

[sunny_talwar]

Look at my response below.... I have provided two options

[jim_chan]

i have used option 1 before you posted up. yes. left join seems working fine, bro.

Thanks bro Sunny!