X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 855BC52828 for ; Thu, 16 Jun 2005 10:28:42 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 46353-04 for ; Thu, 16 Jun 2005 13:28:39 +0000 (GMT) Received: from WM12.inbox.com (wm12.inbox.com [208.50.6.12]) by svr1.postgresql.org (Postfix) with SMTP id 8D5F05283F for ; Thu, 16 Jun 2005 10:28:37 -0300 (ADT) Received: from inbox.com (127.0.0.1:25) by inbox.com with [InBox.Com ESMTP Server] id <506160009690.WM12> for from ; Thu, 16 Jun 2005 5:28:38 AM -0800 Mime-Version: 1.0 Date: Thu, 16 Jun 2005 05:28:37 -0800 Message-ID: <04B61B094B2.000000EFs.gnanavel@inbox.com> From: Gnanavel Shanmugam Subject: Re: SELECT with sum on groups ORDERING by the subtotals To: pgsql-sql@postgresql.org In-Reply-To: <42B17983.20606@carvalhaes.net> References: X-Mailer: IBISIT WebMail Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.023 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/192 X-Sequence-Number: 21793 This might work, select * from (SELECT * FROM product_sales()) t order by t.code desc,t.subtotal; with regards, S.Gnanavel > -----Original Message----- > From: grupos=40carvalhaes.net > Sent: Thu, 16 Jun 2005 10:07:15 -0300 > To: s.gnanavel=40inbox.com, pgsql-sql=40postgresql.org > Subject: Re: =5BSQL=5D SELECT with sum on groups ORDERING by the subtotals > > Hi =21 > > This is not possible because the query will have a time interval and the > subtotal will change due the intervals passed to the query... > To get the subtotal I already know how to do it (see below) but the > problem is get the correct output, ordering by the bigger totals > agrouped by product code > > CREATE TYPE subtotal_type AS > (code varchar(15), > description varchar(60), > quant float8, > price float8, > total float8, > subtotal float8); > > > CREATE OR REPLACE FUNCTION product_sales() > RETURNS SETOF subtotal_type AS > =24BODY=24 > DECLARE > tbrow RECORD; > sbrow subtotal_type; > > BEGIN > sbrow.subtotal :=3D 0; > FOR tbrow IN > SELECT code, description, quant, price, total FROM test ORDER BY code > LOOP > > IF sbrow.code =3D tbrow.code THEN > sbrow.subtotal :=3D sbrow.subtotal + tbrow.total; > ELSE > sbrow.subtotal :=3D tbrow.total; > END IF; > sbrow.code :=3D tbrow.code; > sbrow.description :=3D tbrow.description; > sbrow.quant :=3D tbrow.quant; > sbrow.price :=3D tbrow.price; > sbrow.total :=3D tbrow.total; > > RETURN NEXT sbrow; > END LOOP; > > > RETURN; > > END; > =24BODY=24 > LANGUAGE 'plpgsql' VOLATILE; > > With this function my output is: > > dadosadv=3D=23 SELECT * FROM product_sales(); > code =7C description =7C quant =7C price =7C total =7C subtotal > -------+-------------+-------+-------+-------+---------- > 92110 =7C PRODUCT A =7C 10 =7C 1 =7C 10 =7C 10 > 92110 =7C PRODUCT A =7C 5 =7C 0.9 =7C 9 =7C 19 > 92110 =7C PRODUCT A =7C 100 =7C 0.9 =7C 90 =7C 109 > 92110 =7C PRODUCT A =7C 10 =7C 1.1 =7C 11 =7C 120 > 92190 =7C PRODUCT b =7C 10 =7C 1.1 =7C 11 =7C 11 > 92190 =7C PRODUCT b =7C 10 =7C 1.1 =7C 11 =7C 22 > 92190 =7C PRODUCT b =7C 10 =7C 1.1 =7C 11 =7C 33 > 92190 =7C PRODUCT b =7C 20 =7C 0.8 =7C 8 =7C 41 > 99120 =7C PRODUCT C =7C 10 =7C 0.8 =7C 8 =7C 8 > 99120 =7C PRODUCT C =7C 100 =7C 0.8 =7C 80 =7C 88 > 99120 =7C PRODUCT C =7C 200 =7C 0.8 =7C 160 =7C 248 > 99120 =7C PRODUCT C =7C 100 =7C 0.9 =7C 90 =7C 338 > (12 rows) > > The only problem that I have is that I need to order by the > max(subtotal) aggrouped by code. My desired output is: > > code =7C description =7C quant =7C price =7C total =7C subtotal > -------+-------------+-------+-------+-------+---------- > 99120 =7C PRODUCT C =7C 10 =7C 0.8 =7C 8 =7C 8 > 99120 =7C PRODUCT C =7C 100 =7C 0.8 =7C 80 =7C 88 > 99120 =7C PRODUCT C =7C 200 =7C 0.8 =7C 160 =7C 248 > 99120 =7C PRODUCT C =7C 100 =7C 0.9 =7C 90 =7C 338 > 92110 =7C PRODUCT A =7C 10 =7C 1 =7C 10 =7C 10 > 92110 =7C PRODUCT A =7C 5 =7C 0.9 =7C 9 =7C 19 > 92110 =7C PRODUCT A =7C 100 =7C 0.9 =7C 90 =7C 109 > 92110 =7C PRODUCT A =7C 10 =7C 1.1 =7C 11 =7C 120 > 92190 =7C PRODUCT b =7C 10 =7C 1.1 =7C 11 =7C 11 > 92190 =7C PRODUCT b =7C 10 =7C 1.1 =7C 11 =7C 22 > 92190 =7C PRODUCT b =7C 10 =7C 1.1 =7C 11 =7C 33 > 92190 =7C PRODUCT b =7C 20 =7C 0.8 =7C 8 =7C 41 > > Any tip? > > Regards, > > Rodrigo Carvalhaes > > > Gnanavel Shanmugam wrote: > > >I think it will be better to add one more column for subtotal and > >write an =22on before insert=22 trigger to update the subtotal with sum = of > >total. > > > > > > > > > >>-----Original Message----- > >>From: grupos=40carvalhaes.net > >>Sent: Thu, 16 Jun 2005 00:56:42 -0300 > >>To: pgsql-sql=40postgresql.org > >>Subject: =5BSQL=5D SELECT with sum on groups ORDERING by the subtotals > >> > >>Hi Guys=21 > >> > >>I need to make a complex query. I am thinking to use plpgsql BUT I am > >>confused how I can solve this. > >> > >>What I have: > >>CREATE TABLE test > >>( > >> code varchar(15), > >> description varchar(60), > >> group varchar(10), > >> quant float8, > >> price float8, > >> total float8 > >>) > >>WITHOUT OIDS; > >> > >>INSERT INTO test (code, description, quant, price, total) VALUES > >>('92110', 'PRODUCT A', 10, 1, 10); > >>INSERT INTO test (code, description, quant, price, total) VALUES > >>('92110', 'PRODUCT A', 5, 0.90, 9); > >>INSERT INTO test (code, description, quant, price, total) VALUES > >>('92110', 'PRODUCT A', 100, 0.9, 90); > >>INSERT INTO test (code, description, quant, price, total) VALUES > >>('92110', 'PRODUCT A', 10, 1.1, 11); > >>INSERT INTO test (code, description, quant, price, total) VALUES > >>('92190', 'PRODUCT b', 10, 1.1, 11); > >>INSERT INTO test (code, description, quant, price, total) VALUES > >>('92190', 'PRODUCT b', 10, 1.1, 11); > >>INSERT INTO test (code, description, quant, price, total) VALUES > >>('92190', 'PRODUCT b', 10, 1.1, 11); > >>INSERT INTO test (code, description, quant, price, total) VALUES > >>('92190', 'PRODUCT b', 20, 0.8, 8); > >>INSERT INTO test (code, description, quant, price, total) VALUES > >>('99120', 'PRODUCT C', 10, 0.8, 8); > >>INSERT INTO test (code, description, quant, price, total) VALUES > >>('99120', 'PRODUCT C', 100, 0.8, 80); > >>INSERT INTO test (code, description, quant, price, total) VALUES > >>('99120', 'PRODUCT C', 200, 0.8, 160); > >>INSERT INTO test (code, description, quant, price, total) VALUES > >>('99120', 'PRODUCT C', 100, 0.9, 90); > >> > >> > >>I need an subtotal for all the products with the same group and that > the > >>query be ordered by the bigger subtotal. > >> > >>For example, I need an output like this: > >>Ex. > >> > >> > >> code =7C description =7C quant =7C price =7C total =7C subtotal > >>-------+-------------+-------+-------+-------+---------- > >> 99120 =7C PRODUCT C =7C 10 =7C 0.8 =7C 8 =7C 8 > >> 99120 =7C PRODUCT C =7C 100 =7C 0.8 =7C 80 =7C 88 > >> 99120 =7C PRODUCT C =7C 200 =7C 0.8 =7C 160=7C 168 > >> 99120 =7C PRODUCT C =7C 100 =7C 0.9 =7C 90 =7C 667 > >> 92110 =7C PRODUCT A =7C 10 =7C 1 =7C 10 =7C 10 > >> 92110 =7C PRODUCT A =7C 5 =7C 0.9 =7C 9 =7C 19 > >> 92110 =7C PRODUCT A =7C 100 =7C 0.9 =7C 90 =7C 109 > >> 92110 =7C PRODUCT A =7C 10 =7C 1.1 =7C 11 =7C 120 > >> 92190 =7C PRODUCT b =7C 10 =7C 1.1 =7C 11 =7C 11 > >> 92190 =7C PRODUCT b =7C 10 =7C 1.1 =7C 11 =7C 22 > >> 92190 =7C PRODUCT b =7C 10 =7C 1.1 =7C 11 =7C 33 > >> 92190 =7C PRODUCT b =7C 20 =7C 0.8 =7C 8 =7C 41 > >> > >>The subtotal column must sum all the products with the same code and > put > >>the result in order of the bigger sultotals. > >> > >>Only make a function that sum the last value + the subtotal it's not > >>hard BUT how I can make the subtotal restart when the code changes and > >>how I will order the result by the bigger subtotal code groups? > >> > >>Thanks=21 > >> > >>Rodrigo Carvalhaes > >> > >>-- > >>Esta mensagem foi verificada pelo sistema de antiv=C3=ADrus e > >> acredita-se estar livre de perigo. > >> > >> > >>---------------------------(end of > broadcast)--------------------------- > >>TIP 7: don't forget to increase your free space map settings > >> > >> > > > > > >with regards, > >S.Gnanavel > > > > > > -- > Esta mensagem foi verificada pelo sistema de antivrus e > acredita-se estar livre de perigo. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq=