X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 5AEEC9FA50D for ; Thu, 29 Jun 2006 01:57:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 06946-04 for ; Thu, 29 Jun 2006 01:57:06 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.169]) by postgresql.org (Postfix) with ESMTP id BE6659FA395 for ; Thu, 29 Jun 2006 01:57:05 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so141049ugf for ; Wed, 28 Jun 2006 21:57:04 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=CsGUGzfhmIZsr9nfy6KXIoQvEm4AArwxaPVtLatGgv8EbwYF2XxQ4P+Gg0fo210UuW2MX7HZPhX710JjD79unm0OxhDXTtSK7ZUGTDjMAoavMIbxWF3Az6Swpwsm+BnxlyyLJ7SZ6FV3sR4KG82f/+8Ip/JTSdJkDqXzDioOV9o= Received: by 10.78.117.10 with SMTP id p10mr890285huc; Wed, 28 Jun 2006 21:57:03 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Wed, 28 Jun 2006 21:57:03 -0700 (PDT) Message-ID: Date: Wed, 28 Jun 2006 23:57:03 -0500 From: "Aaron Bono" To: "Phillip Smith" Subject: Re: SELECT Aggregate Cc: pgsql-sql@postgresql.org In-Reply-To: <00d001c69b33$bfa13b90$9b0014ac@ITPhil> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13377_26354830.1151557023355" References: <00d001c69b33$bfa13b90$9b0014ac@ITPhil> X-Google-Sender-Auth: 2ba29c0a64b99113 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/308 X-Sequence-Number: 25544 ------=_Part_13377_26354830.1151557023355 Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 t= o 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=3D'M') AND (sales_orders.trans_no =3D 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 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 column= s. > 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=3D'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 =96 > SUM(soh_product.sell_price) =96 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=3D'M') > > AND (sales_orders.trans_no =3D 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) > > ) > ------=_Part_13377_26354830.1151557023355 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I would recommend against using a function.  If you are selecting a la= rge number of rows, the function will run for each row returned and will ha= ve 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 g= roup by on all columns in the select EXCEPT sale_price should do the trick:=

SELECT      trans_no,

&= nbsp;           customer,

&= nbsp;           date_placed,

&= nbsp;           date_complete,=

&= nbsp;           date_printed,<= /span>

&= nbsp;           ord_type,

    &nbs= p;       ord_status,

    &nbs= p;       SUM(soh_product.sell_price),

    &nbs= p;       customer_reference,

&= nbsp;           salesman,

&= nbsp;           parent_order,<= /span>

&= nbsp;           child_order,

&= nbsp;           order_number

F= ROM        sales_orders, soh_product

W= HERE       (trans_no Like '8%' AND order_number Like '8%')

&= nbsp;OR         (trans_no Like '9%' AND order_number Like '8%')

&= nbsp;OR         (trans_no Like '8%' AND order_number Like '9%')

&= nbsp;OR         (trans_no Like '9%' AND order_number Like '9%')

&= nbsp;AND        (warehouse=3D'M')

  AND        (sales_orders.trans_no =3D soh_product.soh_num)

&= nbsp;AND        (date_placed > (current_date + ('12 months ago'::interval)))

G= ROUP BY    trans_no,

    =         customer,

    &nbs= p;       date_placed,

    &nbs= p;       date_complete,

    &nbs= p;       date_printed,

    &nbs= p;       ord_type,

    &nbs= p;       ord_status,

&nbs= p;           customer_reference,

    &nbs= p;       salesman,

    &nbs= p;       parent_order,

    &nbs= p;       child_order,

    &nbs= p;       order_number

O= RDER 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 com= mon 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:

S= ELECT      trans_no,

&= nbsp;           customer,

&= nbsp;           date_placed,

&= nbsp;           date_complete,=

&= nbsp;           date_printed,<= /span>

&= nbsp;           ord_type,

&= nbsp;           ord_status,

&= nbsp;           customer_refer= ence,

&= nbsp;           salesman,

&= nbsp;           parent_order,<= /span>

&= nbsp;           child_order,

&= nbsp;           order_number

F= ROM        sales_orders

W= HERE       (trans_no Like '8%' AND order_number Like '8%')

&= nbsp;OR         (trans_no Like '9%' AND order_number Like '8%')

&= nbsp;OR         (trans_no Like '8%' AND order_number Like '9%')

&= nbsp;OR         (trans_no Like '9%' AND order_number Like '9%')

&= nbsp;AND        (warehouse=3D'M')

&= nbsp;AND        (date_placed > (current_date + ('12 months ago'::interval)))

O= RDER BY trans_no DESC

 

But I want to add in a wholesale value of each order =96 SUM(soh_product.sell_price) =96 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.

S= ELECT      trans_no,

&= nbsp;           customer,

&= nbsp;           date_placed,

&= nbsp;           date_complete,=

&= nbsp;           date_printed,<= /span>

&= nbsp;           ord_type,

&= nbsp;           ord_status,

&= nbsp;           SUM(soh_product.sell_price),

&= nbsp;           customer_refer= ence,

&= nbsp;           salesman,

&= nbsp;           parent_order,<= /span>

&= nbsp;           child_order,

&= nbsp;           order_number

F= ROM        sales_orders, soh_product

W= HERE       (trans_no Like '8%' AND order_number Like '8%')

&= nbsp;OR         (trans_no Like '9%' AND order_number Like '8%')

&= nbsp;OR         (trans_no Like '8%' AND order_number Like '9%')

&= nbsp;OR         (trans_no Like '9%' AND order_number Like '9%')

&= nbsp;AND        (warehouse=3D'M')

&= nbsp; AND        (sales_orders.trans_no =3D soh_product.soh_num)

&= nbsp;AND        (date_placed > (current_date + ('12 months ago'::interval)))

G= ROUP BY soh_product.soh_num

O= RDER BY trans_no DESC

 

C= REATE TABLE sales_orders

(=

&= nbsp; trans_no varchar(6) NOT NULL,

&= nbsp; customer varchar(6),

&= nbsp; date_placed date,

&= nbsp; date_complete date,

&= nbsp; date_printed date,

&= nbsp; ord_type varchar(1),

&= nbsp; ord_status varchar(1),

&= nbsp; discount float8,

&= nbsp; customer_reference text,

&= nbsp; warehouse varchar(3),

&= nbsp; salesman varchar(3),

&= nbsp; username text,

&= nbsp; ordered_value float8 DEFAULT 0,

&= nbsp; supplied_value float8 DEFAULT 0,

&= nbsp; ordered_qty int8,

&= nbsp; supplied_qty int8 DEFAULT 0,

&= nbsp; frieght float8 DEFAULT 0,

&= nbsp; delivery_instructions text,

&= nbsp; parent_order varchar(6),

&= nbsp; child_order varchar(6),

&= nbsp; apply_to_order varchar(6),

&= nbsp; fo_release date,

&= nbsp; order_number varchar(6),

&= nbsp; orig_fo_number varchar(6),

&= nbsp; CONSTRAINT soh_pkey PRIMARY KEY (trans_no)

)=

C= REATE TABLE soh_product

(=

&= nbsp; soh_num varchar(6) NOT NULL,

&= nbsp; prod_code varchar(6) NOT NULL,

&= nbsp; qty_ordered numeric(8),

&= nbsp; qty_supplied numeric(8),

&= nbsp; cost_price numeric(10,2),

&= nbsp; sell_price numeric(10,2),

&= nbsp; sales_tax numeric(10,2),

&= nbsp; discount numeric(10,2),

&= nbsp; cost_gl varchar(5),

&= nbsp; if_committed varchar(1)

)=

------=_Part_13377_26354830.1151557023355--