Load sum group by

[ecabanas]

Hi,

I have a table like this:

created_at	Custaccount	total
04/01/2016 10:11	C000003	79,55
18/01/2016 11:00	C000003	103,42
08/02/2016 11:08	C000003	137,77
23/02/2016 15:36	C000003	133,43
01/03/2016 12:34	C000003	74,63
11/03/2016 12:07	C000003	64,46
11/03/2016 19:36	C000003	17,95

I want to sum the total per month and group by custaccount in load script.

for example:

C00003 jan-16: 182,97€

C00003 feb-16: 271,20€

Many thank's

Eduard

[sunny_talwar]

Try something like this:

Table:

LOAD created_at,

  MonthName(created_at) as MonthYear,

    Custaccount,

    total

FROM

test_file.xls

(biff, embedded labels, table is Sheet1$);

Join (Table)

LOAD MonthYear,

  Sum(total) as [Sum of Total]

Resident Table

Group By MonthYear;

[ecabanas]

Hi Sunny

The totals did not sum correctly, see attached the project and the excel file

Many thank's

Eduard

[sunny_talwar · Accepted solution]

May be you want it grouped by customer account and MonthYear:

Join([Customer_sales])

Load MonthYear,

  Custaccount,

  Sum(total) as Gasto

Resident [Customer_sales]

Group by MonthYear, Custaccount;

[ecabanas]

Yessss perfect!!! many thank's again Sunny....i have another for you see next post 🙂

[jagathi]

How do we use Where clause for this case? 

Say we have loaded the input

get the sum of sales based on group by customer and month year

After this, I need to see the sales details which are more than 20,000 only 

Then how do I apply the Where clause sum(sales)>20,000 for this script