Calculate a grand total

[Anonymous]

Agg_Pha_Tmp:

  Load

  MEM#,              

  sum(PhaTotal) as SumPhaTotal

Resident pha               

group by MEM#;

Agg_Med_Tmp:               

Load

  MEM#,              

  sum(Total) as SumMedTotal

Resident med              

group by MEM#;

I am wondering how i can sum the SumPhaTotal and SumMedTotal (SumPhaTotal + SumMedTotal) to get a GrandTotal.

Thanks.

[erichshiino]

It could be like this:

Agg_Pha_Tmp:

  Load

  MEM#,              

  sum(PhaTotal) as SumPhaTotal

Resident pha               

group by MEM#;

Agg_Med_Tmp:               

Load

  MEM#,              

  sum(Total) as SumMedTotal

Resident med              

group by MEM#;

tAggr_Tmp:

Load MEM#, SumPhaTotal as SumTotal

resident Agg_Pha_Tmp;

concatenate (tAggr_Tmp)

Load MEM#, SumMedTotalas SumTotal

resident Agg_Pha_Med;

Aggr_Tmp:

Load MEM#, sum(SumTotal) as SumTotal

resident tAggr_Tmp

group by MEM#;

drop table tAggr_Tmp;

Hope this helps,

Erich

[johnw · Accepted solution]

I'd load them as one table instead of two from the very start.

Data:
LOAD MEM#, sum(PhaTotal) as SumPhaTotal
RESIDENT pha
GROUP BY MEM#
;
OUTER JOIN (Data)
LOAD MEM#, sum(Total) as SumMedTotal
RESIDENT med
GROUP BY MEM#
;
LEFT JOIN (Data)
LOAD MEM#, rangesum(SumPhaTotal,SumMedTotal) as GrandTotal
RESIDENT Data
;

[Anonymous]

John,

Thank you. It works.