Re: SELECT Aggregate

I've tried Aaron's suggestion of the GROUP BY and I don't know much about
it, but it ran for around 17 hours and still going (it had a dedicated Dual
Xeon 3.0GHz box under RHEL4 running it!)

I'll give Richard's suggestion a try and see if that comes up any better.
Like I said yesterday, this might just be too much for Postgres and I'll
need to summarize it in the export from our live system and add a new column
before I import it to the sales_orders table

Cheers,

-p

-----Original Message-----
From: aaron(dot)bono(at)gmail(dot)com [mailto:aaron(dot)bono(at)gmail(dot)com] On Behalf Of Aaron
Bono
Sent: Friday, 30 June 2006 01:25
To: Richard Broersma Jr
Cc: Phillip Smith; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] SELECT Aggregate

I am not familiar enough with how postgres optimizes the queries but won't
this end up with

total number of queries run on DB = 1 query + 1 query/row in first query

What would be more efficient on a large database - a query like Richard
submitted (subquery in the select) or one like I submitted (join the two
tables and then do a group by)? My guess is it depends on the % of records
returned out of the sales_orders table, the smaller the % the better
Richard's query would perform, the higher the % the better the join would
run.

The database I am working with aren't big enough yet to warrant spending a
lot of time researching this but if someone with more experience knows what
is best I would love to hear about it.

Thanks,
Aaron Bono

On 6/29/06, Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:

> SELECT trans_no,
> customer,
> date_placed,
> date_complete,
> date_printed,
> ord_type,
> ord_status,
select (
SUM(sell_price)
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

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments