Archives of the TeradataForum
Message Posted: Fri, 13 Feb 2004 @ 23:52:38 GMT
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
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|