Archives of the TeradataForum

Message Posted: Fri, 13 Feb 2004 @ 23:52:38 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I have a SQL running in an XML page that has nearly 50 select-list items. I need to add a new item (51st) in the select-list without touching the FROM, WHERE, GROUP BY, ORDER BY clauses. In otherwords, I can do whatever I want in the column that I create without touching anywhere else in the SQL.

Now, here is my question:

I need to find the Average of two columns based on a third column. I can illustrate it like this. There is a FACT table called FA_CDR_MEASUREMENTS. Its first column is Region-ID. Second Column is Market-ID. Third Column is Quantity. Fourth Column is Amount. I need to calculate the Average for each Market-ID as Avg = Sum(Amount)/Sum(Quantity). That means the average needs to be calculated for each Market-ID separately. Then, a final Average at the Region-Level is computed from all the averages calculated at the Market-level. This final average needs to display as the 51st item in my select-list. (The above algorithm is a business requirement).

I cannot change anywhere in the SQL, because the other 50 items are such complex things and that work now. The GROUP BY is REGION-ID.

Any idea on how to resolve this.... Analytical functions, cascaded simple functions etc.

Thanks in advance

Ganga Palakattil

	<Prev	Next>		<<First	<Prev	Next>	Last>>