From: Aaron Bono <postgresql@aranya.com>
To: Phillip Smith <phillips@weatherbeeta.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT Aggregate
Date: Wed, 28 Jun 2006 23:57:03 -0500
Message-ID: <bf05e51c0606282157r23117968pb1f23c191cff2d19@mail.gmail.com> (raw)
In-Reply-To: <00d001c69b33$bfa13b90$9b0014ac@ITPhil>
References: <00d001c69b33$bfa13b90$9b0014ac@ITPhil>
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@weatherbeeta.com.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)
>
> )
>
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: postgresql@aranya.com, phillips@weatherbeeta.com.au
Subject: Re: SELECT Aggregate
In-Reply-To: <bf05e51c0606282157r23117968pb1f23c191cff2d19@mail.gmail.com>
* 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