Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id DBFED9FB279 for ; Thu, 11 Jan 2007 15:51:56 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 90004-04 for ; Thu, 11 Jan 2007 15:51:47 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 1FD0F9FB284 for ; Thu, 11 Jan 2007 15:51:46 -0400 (AST) Received: (qmail 19034 invoked by uid 500); 11 Jan 2007 19:51:44 -0000 Date: Thu, 11 Jan 2007 13:51:44 -0600 From: Bruno Wolff III To: John Summerfield Cc: pgsql-sql@postgresql.org Subject: Re: How to aggregates this data Message-ID: <20070111195144.GA17354@wolff.to> Mail-Followup-To: Bruno Wolff III , John Summerfield , pgsql-sql@postgresql.org References: <45A56DAB.1030709@herakles.homelinux.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <45A56DAB.1030709@herakles.homelinux.org> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/32 X-Sequence-Number: 27184 On Thu, Jan 11, 2007 at 07:50:19 +0900, John Summerfield wrote: > > I've perused my book (Mastering SQL by Martin Gruber), the postgresql > docs (I have versions here on RHEL (Centos) 4, FC5,6, Debian Testing - > up to 8,1) and I don't see how to choose the entry for the first open > column, the last close. Max, min and average will do nicely for the > others, and converting the date to an interval then dividing by seven > seems to work nicely for creating my groups, > > The results I expect from the above data are > TLSCA 2006-12-07 2.330 2.450 2.280 2.450 mumble > TLSCA 2006-12-14 2.450 2.650 2.450 2.620 mumble > > The question is, "How can I do this in SQL?" There are date functions that return the week of the year, so that you could do a group by week and year to get your aggregates. To get the open and close prices, you could join back to the table (twice) on week, year and either the first or last day of the week. As long as there is data for every day of the week for every stock, this should work.