Re: SELECT Aggregate

I would recommend against using a function. If you are selecting a large
number of rows, the function will run for each row returned and will have to
do a select for each row. So if you get 1000 rows returned from your query,
you will end up with 1001 select statements for your one query.

Assuming trans_no is your primary key (or at least unique) then a group by
on all columns in the select EXCEPT sale_price should do the trick:

SELECT trans_no,

customer,

date_placed,

date_complete,

date_printed,

ord_type,

ord_status,

SUM(soh_product.sell_price),

customer_reference,

salesman,

parent_order,

child_order,

order_number

FROM sales_orders, soh_product

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 (sales_orders.trans_no = soh_product.soh_num)

AND (date_placed > (current_date + ('12 months ago'::interval)))

GROUP BY trans_no,

customer,

date_placed,

date_complete,

date_printed,

ord_type,

ord_status,

customer_reference,

salesman,

parent_order,

child_order,

order_number

ORDER BY trans_no DESC

On 6/28/06, Phillip Smith <phillips(at)weatherbeeta(dot)com(dot)au> wrote:
>
> Hi all,
>
> I have two tables which are storing all our sales orders / invoices as
> below. sales_order.trans_no and soh_product.soh_num are the common columns.
> This is PostgreSQL 8.1.4 (ie, the latest release)
>
>
>
> We have some issues that I've been able to identify using this SELECT:
>
> SELECT trans_no,
>
> customer,
>
> date_placed,
>
> date_complete,
>
> date_printed,
>
> ord_type,
>
> ord_status,
>
> 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
>
>
>
> But I want to add in a wholesale value of each order –
> SUM(soh_product.sell_price) – How would be best to do this? Would it be
> easiest to create a function to accept the trans_no then do a SELECT on
> soh_product and return that value?
>
>
>
> Thanks,
>
> -p
>
>
>
> I've tried to do this but Postgres complains about having to include all
> the other columns in either an aggregate or the GROUP BY.
>
> SELECT trans_no,
>
> customer,
>
> date_placed,
>
> date_complete,
>
> date_printed,
>
> ord_type,
>
> ord_status,
>
> SUM(soh_product.sell_price),
>
> customer_reference,
>
> salesman,
>
> parent_order,
>
> child_order,
>
> order_number
>
> FROM sales_orders, soh_product
>
> 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 (sales_orders.trans_no = soh_product.soh_num)
>
> AND (date_placed > (current_date + ('12 months ago'::interval)))
>
> GROUP BY soh_product.soh_num
>
> ORDER BY trans_no DESC
>
>
>
> CREATE TABLE sales_orders
>
> (
>
> trans_no varchar(6) NOT NULL,
>
> customer varchar(6),
>
> date_placed date,
>
> date_complete date,
>
> date_printed date,
>
> ord_type varchar(1),
>
> ord_status varchar(1),
>
> discount float8,
>
> customer_reference text,
>
> warehouse varchar(3),
>
> salesman varchar(3),
>
> username text,
>
> ordered_value float8 DEFAULT 0,
>
> supplied_value float8 DEFAULT 0,
>
> ordered_qty int8,
>
> supplied_qty int8 DEFAULT 0,
>
> frieght float8 DEFAULT 0,
>
> delivery_instructions text,
>
> parent_order varchar(6),
>
> child_order varchar(6),
>
> apply_to_order varchar(6),
>
> fo_release date,
>
> order_number varchar(6),
>
> orig_fo_number varchar(6),
>
> CONSTRAINT soh_pkey PRIMARY KEY (trans_no)
>
> )
>
> CREATE TABLE soh_product
>
> (
>
> soh_num varchar(6) NOT NULL,
>
> prod_code varchar(6) NOT NULL,
>
> qty_ordered numeric(8),
>
> qty_supplied numeric(8),
>
> cost_price numeric(10,2),
>
> sell_price numeric(10,2),
>
> sales_tax numeric(10,2),
>
> discount numeric(10,2),
>
> cost_gl varchar(5),
>
> if_committed varchar(1)
>
> )
>