DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=wredSFWscmFFb3Sg4PweofPkkU1Ls4QRTALbM5ymEm9xbtOKN8GbZuChAqYbmQcLyVp4KN/FkHqFk/wjww+AgVtQgRxlxEV4natQpyFjYO0LkzTpdF6a1N0+IMRQEQxxNCyQxlfw2qjs++rwdqOwGlqXa6IaSi36PA7UAHR8nuw=
	;
Message-ID: <20060629053938.4326.qmail@web31802.mail.mud.yahoo.com>
Date: Wed, 28 Jun 2006 22:39:38 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: SELECT Aggregate
To: Richard Broersma Jr <rabroersma@yahoo.com>,
	Phillip Smith <phillips@weatherbeeta.com.au>, pgsql-sql@postgresql.org
In-Reply-To: <20060629052335.51714.qmail@web31812.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

> > SELECT      trans_no,
> >             customer,
> >             date_placed,
> >             date_complete,
> >             date_printed,
> >             ord_type,
> >             ord_status,
                (select  SUM(sell_price)   -- this syntax working for me. see below
                 from    soh_product
                 where   sales_orders.trans_no = soh_product.soh_num
                 ) as transact_sum,
> >             customer_reference,
> >             salesman,
> >             parent_order,
> >             child_order,
> >             order_number
> > FROM        sales_orders
> > WHERE       (trans_no Like '8%' AND order_number Like '8%')
> >  OR         (trans_no Like '9%' AND order_number Like '8%')
> >  OR         (trans_no Like '8%' AND order_number Like '9%')
> >  OR         (trans_no Like '9%' AND order_number Like '9%')
> >  AND        (warehouse='M')
> >  AND        (date_placed > (current_date + ('12 months ago'::interval)))
> > ORDER BY trans_no DESC
> 
> 
> I am pretty new to SQL.  But while reading a book written by an author recommended on this
> list,I
> can suggest a possible solution that I've seen.  It might work for your problem. Of course, I
> haven't tested anything like this and don't know if PostgreSQL supports it.
> 
> Just be sure that trans_no is unique in the returned query.

select
        f1.fiscalyear,

        (select f2.startdate
         from   fiscalyeartable2 as f2
         where  f1.fiscalyear = f2.fiscalyear
        ) as start2date,

        f1.enddate

from
        fiscalyeartable1 as f1;

 fiscalyear | start2date |  enddate
------------+------------+------------
       1995 | 1994-10-01 | 1995-09-30
       1996 | 1995-10-01 | 1996-08-30
       1997 | 1996-10-01 | 1997-09-30
       1998 | 1997-10-01 | 1998-09-30
 

                     QUERY PLAN                                    
--------------------------------------------------------------------------
 Seq Scan on fiscalyeartable1 f1  (cost=0.00..6.83 rows=1 width=6) 
                                  (actual time=0.044..0.067 rows=4 loops=1)
   SubPlan
     ->  Index Scan using fiscalyeartable2_pkey on fiscalyeartable2 f2  
                                  (cost=0.00..5.82 rows=1 width=4) 
                                  (actual time=0.008..0.009 rows=1 loops=4)
           Index Cond: ($0 = fiscalyear)
 Total runtime: 0.138 ms
(5 rows)

it works,  and check out the nifty query plan.

Regards,

Richard Broersma Jr.