SELECT Aggregate

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)

)

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