Why do I get an invalid expression error when trying to use sum and group by?

[#1]

I have the following as my load script.

LOAD text(inventory_num) as inv_num, sum(amt_paid) as amt_paid_2014_06 FROM C:\Downloads\invpaid.txt (txt, codepage is 1252, embedded labels, delimiter is '~') GROUP by inv_num;

The invpaid.txt file has inventory numbers and the amount paid for each number. Sometimes there are multiple payments per inventory number. I want to consolidate the data so that I have only unique inventory numbers with the total amount paid for each number.

For example

inv_num	amt_paid
a1	30
a1	31
a2	30

The above table should turn into

inv_num	amt_paid
a1	61
a2	30

[alexandros17]

Try this:

LOAD text(inventory_num) as inv_num, sum(amt_paid) as amt_paid_2014_06 FROM C:\Downloads\invpaid.txt (txt, codepage is 1252, embedded labels, delimiter is '~') GROUP by text(inventory_num);

you cannot use alias ...

[Anonymous · Accepted solution]

Try this :

LOAD text(inventory_num) as inv_num, sum(amt_paid) as amt_paid_2014_06 FROM C:\Downloads\invpaid.txt (txt, codepage is 1252, embedded labels, delimiter is '~') GROUP by inventory_num ;

[simenkg]

You are grouping by a field that has not yet been defined.

try:

LOAD text(inventory_num) as inv_num, sum(amt_paid) as amt_paid_2014_06 FROM C:\Downloads\invpaid.txt (txt, codepage is 1252, embedded labels, delimiter is '~') GROUP by inventory_num;

Regards
SKG

[#5]

Thanks Bill! Your solution worked.