Re: SELECT Aggregate

> > 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.83rows=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=1width=4)                                  (actual time=0.008..0.009 rows=1 loops=4)          Index Cond: ($0 =
fiscalyear)Totalruntime: 0.138 ms
 
(5 rows)

it works,  and check out the nifty query plan.

Regards,

Richard Broersma Jr.