From: Phillip Smith <phillips@weatherbeeta.com.au>
To: pgsql-sql@postgresql.org
Subject: SELECT Aggregate
Date: Thu, 29 Jun 2006 14:23:18 +1000
Message-ID: <00d001c69b33$bfa13b90$9b0014ac@ITPhil> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: phillips@weatherbeeta.com.au
Subject: Re: SELECT Aggregate
In-Reply-To: <00d001c69b33$bfa13b90$9b0014ac@ITPhil>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox