[SQL] SELECT Aggregate

[Phillip Smith]

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