Sum of each column to be printed in last row

[harshada]

Hi,

I want to print sum of some of the columns as the last row whcih shows the total of all the data coming in a particular column.

for eg
LA Vol1 Date Vol2 Vol3 Status
A 10 23/Feb/2008 5 10 F
B 20 23/Feb/2008 5 10 F
C 10 23/Feb/2008 5 10 P
D 30 23/Feb/2008 5 10 P
4 70 20 50

The last row gives the total of col data except for date column and status column. the data is like
4 is sum of count of LA
70 is sum of Vol1
Below date col it will be null
20 is sum of Vol2
50 is sum of Vol3
Below Status col it will be null

How can I get this sum printed only in last row.
If I use aggregator to seperately sum each column, how will i get the other 2 columns all in a same row and printed at last?

Thanks,
Harshada

[hamzaqk]

Why dont you split the records and feed each column into a aggregator and then get row count for each. and then merge them at the end with the row count??

[DS_SUPPORT]

You can do this at the database itself.

Code: Select all

SELECT LA,VOL1,DATE,VOL2,VOL3,STAUS,dummycol FROM
(
SELECT LA,VOL1,DATE,VOL2,VOL3, STATUS, 0 dummycol FROM YOUR TABLE
UNION
SELECT SUM(LA),SUM(VOL1),NULL, SUM(VOL2), SUM(VOL3),NULL,1 dummycol FROM YOURTABLE
) dummytable
order by dummycol 

I dint tested it, take this as startup and proceed.

[chulett]

Send those columns to an Aggregator, sum, write to a different file and then append (cat x >> y) that file to your 'main' file after job.

[harshada]

Thanks for your responses. I am now trying to do same thing.. aggregate seperately then using transformer (pseudo join key) join all aggregator data then cat the main file and the last line.

chulett, if I try your logic.. and cat, i will get each sum as a seperate row, i need the sum columns side by side.

But thanks anyways to you all.

[chulett]

No, you won't. What makes you think you need to aggregate each column separately? Send them all in the link and only one record will come out since you'll be doing no grouping, only summing and counting.

[ray.wurlod]

DataStage was never intended to be a reporting tool.

Use a reporting tool - maybe a BI tool, maybe a spreadsheet tool - for this.