Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 14 Feb 2004 @ 01:01:35 GMT


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


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



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023