list serveral sum() of several tables in 1 columm

[#1]

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

[tresesco · Accepted solution]

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';

[Anonymous]

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.

[#4]

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;

?

[tresesco]

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)

[#6]

thank you very much