Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys
I have several sum()s like
T1:
load x,y,z,
sum(x) as AMOUNT100 where (z<=100and y<=100) group by x,y,z;
SQL SELECT x,y,z
FROM DB9;
T2:
load x,y,z,
sum(x) as AMOUNT200where( (z<=200 and z>100) and (y<=123)) group by x,y,z;
SQL SELECT x,y,z
FROM DB9;
T3:
load x,y,z,
sum(x) as AMOUNT300 where( (z<=300 and z>200) and (y<=123)) group by x,y,z;
SQL SELECT x,y,z
FROM DB9;
My questions are:
1. Is it possible to take all sum() in one table?
2. How can I list all sum() in 1 collumm to have a list like : -> for showing on dasboard ..
AMOUNTLIST, sum
AMOUNT100, 10
AMOUNT200, 20
AMOUNT300, 30
Try like:
T1:
load * SQL SELECT x,y,z FROM DB9;
T2:
load x,y,z,
'AMOUNT100' as AmountList ,
sum(x) as Sum
Resident T1 where (z<=100and y<=100) group by x,y,z;
load x,y,z,
'AMOUNT200' as AmountList,
sum(x) as Sum
Resident T1 where( (z<=200 and z>100) and (y<=123)) group by x,y,z;
load x,y,z,
'AMOUNT300' as AmountList,
sum(x) as Sum
Resident T1 where( (z<=300 and z>200) and (y<=123)) group by x,y,z;
Drop Table T1:
Update: where clause could not be used in preceding load, corrcetd accordingly.
The tables would get automatically concatenated to generate one table named 'T2';
Try like:
T1:
load * SQL SELECT x,y,z FROM DB9;
T2:
load x,y,z,
'AMOUNT100' as AmountList ,
sum(x) as Sum
Resident T1 where (z<=100and y<=100) group by x,y,z;
load x,y,z,
'AMOUNT200' as AmountList,
sum(x) as Sum
Resident T1 where( (z<=200 and z>100) and (y<=123)) group by x,y,z;
load x,y,z,
'AMOUNT300' as AmountList,
sum(x) as Sum
Resident T1 where( (z<=300 and z>200) and (y<=123)) group by x,y,z;
Drop Table T1:
Update: where clause could not be used in preceding load, corrcetd accordingly.
The tables would get automatically concatenated to generate one table named 'T2';
Hi,
Use a different letter or a number as a unique field in every table..
Like load 'i' as k,x,y,z,sum(x) as AMOUNT200where( (z<=200 and z>100) and (y<=123)) group by x,y,z;
Now,concatenate all the tables and u will find the sum(whatever) as a single column
Here the letter 'i' will be used to identify which table the sum belongs to.
HTH
Ravi N.
Dont you mean to rename the sums?
like
T1:
Load...AMOUNT100 as AMOUNTLIST,
sum() as AMOUNT100 where.. group by ..;
SQL SELECT * FROM DB;
Load...AMOUNT200 as AMOUNTLIST,
sum() as AMOUNT200 where.. group by ..;
SQL SELECT * FROM DB;
Load...AMOUNT300 as AMOUNTLIST,
sum() as AMOUNT300 where.. group by ..;
SQL SELECT * FROM DB;
?
As you wanted 'Sum' to be a new column name, my above code would work. Also note my updated script (a corrected one - you can't use where clause in preceding load)
thank you very much