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.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.