Re: SELECT Aggregate - Mailing list pgsql-sql

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)

)