From: Richard Broersma Jr <rabroersma@yahoo.com>
To: Richard Broersma Jr <rabroersma@yahoo.com>
To: Phillip Smith <phillips@weatherbeeta.com.au>
To: pgsql-sql@postgresql.org
Subject: Re: SELECT Aggregate
Date: Wed, 28 Jun 2006 22:39:38 -0700 (PDT)
Message-ID: <20060629053938.4326.qmail@web31802.mail.mud.yahoo.com> (raw)
In-Reply-To: <20060629052335.51714.qmail@web31812.mail.mud.yahoo.com>
> > 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rabroersma@yahoo.com, phillips@weatherbeeta.com.au
Subject: Re: SELECT Aggregate
In-Reply-To: <20060629053938.4326.qmail@web31802.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox