Summing in a Script

[#1]

I have a table that has part number, plant location and quantity.

But I want the script to return only Part number and the "total" quantity for both plants.

How do I sum the to quantity field. I get an error is I use Sum(Quantity) as TotalQty.

Stephen

[Anonymous]

Stephen,
Make sure to add GROUP BY:
LOAD
plant,
sum(quantity) as TotalQty
RESIDENT...
GROUP BY plant;

[#3]

Yes, forgot the Group By.

Thanks

[#4]

Mike,

Do you have to use "Group By" when using Sum() in a load script under a resident Load?  Im trying to sum up a field prior to my Expression.

PV:

NoConcatenate    //Must do this or it will drop all tables at DROP statement

LOAD

     //Text(WBS) as EVM_WBS,

     Text(WBS) as PV_WBS,

     Text([Task Name]) as [PV_Name],

     Date(Num#(PV_Weeks),'DD-MMM-YY') as EVM_Weeks,   

     PV_Amount,

     TBC as PV_TBC

     Sum(TBC) as BAC

Resident tmpPV;

Thanks!

Brian

[Gysbert_Wassenaar]

Yes, if you use an aggregation function you must define the fields over which you want to aggregate. You do that with the group by statement.

[#6]

ok, thank you gwassenaar.  So in my example would I group by: TBC since that's what I'm summing? 

[Gysbert_Wassenaar]

Just the exact opposite The fields in the group by statement are all the fields that are not used in an aggregation function. In this case all fields except TBC which gets aggregated in sum(TBC)

[#8]

Ok, glad I asked. You learn something new every day.

Thanks!