5 Replies Latest reply: Jul 2, 2014 7:33 AM by Tobias Meyer

# list serveral sum() of several tables in 1 columm

Hi guys

I have several sum()s like

T1:

sum(x) as AMOUNT100 where (z<=100and y<=100) group by x,y,z;

SQL SELECT x,y,z

FROM DB9;

T2:

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:

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

• ###### Re: list serveral sum() of several tables in 1 columm

Try like:

T1:

load * SQL SELECT x,y,z FROM DB9;

T2:

'AMOUNT100' as AmountList ,

sum(x) as Sum

Resident T1 where (z<=100and y<=100) group by 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;

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

• ###### Re: list serveral sum() of several tables in 1 columm

Dont you mean to rename the sums?

like

T1:

sum() as AMOUNT100 where.. group by ..;

SQL SELECT * FROM DB;

sum() as AMOUNT200 where.. group by ..;

SQL SELECT * FROM DB;

sum() as AMOUNT300 where.. group by ..;

SQL SELECT * FROM DB;

?

• ###### Re: list serveral sum() of several tables in 1 columm

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)

• ###### Re: list serveral sum() of several tables in 1 columm

thank you very much

• ###### Re: list serveral sum() of several tables in 1 columm

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.