Re: How to aggregates this data

John,<br />   Sub-selects to the rescue.  See below.<br /><br /> select s1.asx_code, s1.bdate AS date, s1.low, s1.high,
s2.open,s3.close, s1.volume<br /> from (select asx_code, date_trunc('week', date) AS bdate, max(date) AS edate,
min(low)AS low, max(high) AS high, sum(volume) AS volume<br />       from sales_summaries<br />       group by
asx_code,date_trunc('week', date)) s1, sales_summaries s2, sales_summaries s3<br /> where s1.bdate = s2.date<br /> and
s1.asx_code=s2.asx_code<br/> and s1.edate = s3.date<br /> and s1.asx_code=s3.asx_code;<br /><br />   asx_code 
|         date          | low  | high | open | close |  volume   <br />
------------+------------------------+------+------+------+-------+-----------<br/>   TLSCA     | 2006-12-04
00:00:00-05| 2.28 | 2.52 | 2.31 |  2.51 | 243406646<br />   TLSCA     | 2006-12-11 00:00:00-05 |  2.5 | 2.65 |  2.5 | 
2.62| 170551800<br /><br /> The "date" is based on ISO-8601 (in other words the week starts on Monday).  Be warned, as
itis questionable how this will scale.  It may require expression (function based) indexes.<br /><br />Oracle has a
featurecalled analytic functions, which would allow you to use functions such as LEAD, LAG, FIRST_VALUE, LAST_VALUE. 
Inparticular FIRST_VALUE and LAST_VALUE would have been useful to determine the open and close for a week, but before
analyticsin Oracle you would use sub-selects or multiple joins.<br /><br /><br />