Row Count in Aggregator

vijayrc · Post by **vijayrc** » Wed Oct 25, 2006 12:48 pm

Hi,
First should thank all the suggestion for my previous questions.
I have an alternate method of arriving at the no of rows used, but my question is : In an Aggregator stage, is there a way to count the no of rows?

e..g, if it's Grouped by A/C Nr and there are 10 records for this A/C No, I would like to know this Number [10]

narasimha · Post by **narasimha** » Wed Oct 25, 2006 12:54 pm

Select the Aggregate Function to be "Count"

talk2shaanc · Post by **talk2shaanc** » Wed Oct 25, 2006 1:11 pm

please read below message

talk2shaanc · Post by **talk2shaanc** » Wed Oct 25, 2006 1:11 pm

I am not aware of your other design detail, so my suggestion would be based on some assumptions.

If ActNo is one of the key columns and is at the higher level, then it would be an additional benefit. Assuming it is; I would sort the data on ActNo and hash partition on ActNo and enable "Create Cluster key change column". In the transformer stage (that you may be having)use "Key Change" column to and define logic to perform aggregation.
An example on cluster key change column: a new column "Key Change" will be added by Datastage and the first row for account number will be 1 and remaining duplicate rows will have 0.
ActNo COLA COLB KeyChange
1 A B 1
1 C D 0
1 E F 0
2 S T 1
2 U v 0
3 W X 1

Aggregator is creepy, I avoid using it; unless its absolutely necessary.

vijayrc · Post by **vijayrc** » Wed Oct 25, 2006 1:59 pm

narasimha wrote:Select the Aggregate Function to be "Count"

But in the same aggregator, I need few columns to be Calculated, and other column to be Row-Counted, and this isn't permitted !?!?

ray.wurlod · Post by **ray.wurlod** » Wed Oct 25, 2006 2:12 pm

Yes it is. You need an additional output column for the count, set it up with Non Missing Values Count (or Missing Values Count) as its calculation function. If you need both, derive both and sum them in a following Transformer stage.

narasimha · Post by **narasimha** » Wed Oct 25, 2006 2:19 pm

Include a count (*) when you are grouping in your source query

select AccountNumber, count(*)
from XXX
group by AccountNumber

count(*) gives you the number of AccountNumber's in your grouping.
Now you can carry this count(*) coulmn forward to your Aggregator, select the Aggregate function say "First".
You can do your calcluations on other columns as you want.