Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 67AA99FA4DF
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 10 Jan 2007 20:38:44 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 52903-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 10 Jan 2007 20:38:29 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.184])
	by postgresql.org (Postfix) with ESMTP id 15A0C9FA4A0
	for <pgsql-sql@postgresql.org>; Wed, 10 Jan 2007 20:38:39 -0400 (AST)
Received: by nf-out-0910.google.com with SMTP id x4so689174nfb
	for <pgsql-sql@postgresql.org>; Wed, 10 Jan 2007 16:38:38 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=lHOQgutdecNCFLw2s1cVZWi90j+syFiq4noDnoNMmVStAmfrLPGI9ze8EnRRlcKbF2SACdKiWre3YQ+iq40wwsPTBQLe6793Y5Ep9/B3pqtJfg4xSF7BXj32h9yvXOgVGNcq1If6GBu7mbTuSwsxCVjXtz9sx0lVcaeUnC6JfgI=
Received: by 10.48.204.7 with SMTP id b7mr1649842nfg.1168475918645;
	Wed, 10 Jan 2007 16:38:38 -0800 (PST)
Received: by 10.48.230.1 with HTTP; Wed, 10 Jan 2007 16:38:38 -0800 (PST)
Message-ID: <81961ff50701101638v68f96965y2310a152d71f3d9@mail.gmail.com>
Date: Wed, 10 Jan 2007 19:38:38 -0500
From: "Chad Wagner" <chad.wagner@gmail.com>
To: "John Summerfield" <postgres@herakles.homelinux.org>
Subject: Re: How to aggregates this data
Cc: pgsql-sql@postgresql.org
In-Reply-To: <81961ff50701101629o265fd555tf64672547261c44@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_25665_19784880.1168475918609"
References: <45A56DAB.1030709@herakles.homelinux.org>
	<81961ff50701101629o265fd555tf64672547261c44@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/28
X-Sequence-Number: 27180

------=_Part_25665_19784880.1168475918609
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

By the way, if you want to include that "incomplete" week before 12/1
(incomplete because it doesn't have a data point for Monday) then you would
do this:


select s1.asx_code, s1.wdate AS date, s1.low, s1.high, s2.open, s3.close,
s1.volume
from (select asx_code, date_trunc('week', date) AS wdate, min(date) AS
bdate, max(date) AS edate, min(low) AS low, max(high) AS high, SUM(volume)
AS volume
      from sales_summaries
      group by asx_code, date_trunc('week', date)) s1, sales_summaries s2,
sales_summaries s3
where s1.bdate = s2.date
and s1.asx_code=s2.asx_code
and s1.edate = s3.date
and s1.asx_code=s3.asx_code;

------=_Part_25665_19784880.1168475918609
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

By the way, if you want to include that &quot;incomplete&quot; week before 12/1 (incomplete because it doesn&#39;t have a data point for Monday) then you would do this:<br><br><br>select s1.asx_code, s1.wdate AS date, s1.low
, s1.high, s2.open, s3.close, s1.volume<br>from (select asx_code, date_trunc(&#39;week&#39;, date) AS wdate, min(date) AS bdate, max(date) AS edate, min(low) AS low, max(high) AS high, SUM(volume) AS volume<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from sales_summaries
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; group by asx_code, date_trunc(&#39;week&#39;, 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><br>

------=_Part_25665_19784880.1168475918609--