 |
 |
Archives of the TeradataForum
Message Posted: Fri, 14 Feb 2004 @ 01:01:35 GMT
| Subj: | | Re: A SQL Question |
| |
| From: | | Victor Sokovin |
| | 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. | |
Try to calculate Avg (I use your notation as above) as something like (SUM (Amount) OVER (PARTITITION BY Region_ID, Market_ID) /
(SUM(Quantity) OVER (PARTITITION BY Region_ID, Market_ID) ).
The per Region_ID average can then be calculated as SUM(Avg) / COUNT (distinct Market_ID), if that's what you mean by it. Hope this will work
when you put it all together in one GROUP BY Region_ID but I did not test it.
Regards,
Victor
| |
