Sums of fields

[Anonymous]

Hi,

I'm working with a table that looks like this:

ID     Material       Quantity
1          A               5
2          B               2
3          B              10
4          C               6
5          C               8
6          C               8

My question is:

Is it possible to modify this table in the loadscript to make it look like this?

Material       Quantity
A               5
B               12
C               22

Thanks in advance!

[sunny_talwar · Accepted solution]

Sure... use this script

AggregatedTable:
LOAD Material,
   Sum(Quantity) as Quantity
FROM/Resident ...
Group By Material;

[Miguel_Angel_Baeyens]

Yes, it should be something like 

Temp: 
LOAD
  Material
  Quantity 
; 
SQL SELECT
  Material
  Quantity
FROM ... 
; 

Table: 
LOAD 
  Material,
  Sum(Quantity) AS Total_Quantity 
RESIDENT Temp 
GROUP BY 
  Material 
 ; 

 The two step LOAD is required when reading from SQL or INLINE. 

Also note that if the volume of data is big, it is likely to be faster to do the Sum() GROUP BY in the SQL statement than in the LOAD part.

[passionate]

this can be easily done with group by clause.

PFA, Solution