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 C4E9552828 for ; Thu, 16 Jun 2005 12:12:47 -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 69810-03 for ; Thu, 16 Jun 2005 15:12:44 +0000 (GMT) Received: from nitrogenio.carvalhaes.net (unknown [200.175.206.50]) by svr1.postgresql.org (Postfix) with ESMTP id 597E052849 for ; Thu, 16 Jun 2005 12:12:41 -0300 (ADT) Received: from [192.168.0.190] ([192.168.0.190]) (authenticated bits=0) by nitrogenio.carvalhaes.net (8.13.4/8.13.4) with ESMTP id j5GF1g0Y015205; Thu, 16 Jun 2005 12:01:42 -0300 Message-ID: <42B19661.2070208@carvalhaes.net> Date: Thu, 16 Jun 2005 12:10:25 -0300 From: grupos User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Gnanavel Shanmugam Cc: pgsql-sql@postgresql.org Subject: Re: SELECT with sum on groups ORDERING by the subtotals References: <04B61B094B2.000000EFs.gnanavel@inbox.com> In-Reply-To: <04B61B094B2.000000EFs.gnanavel@inbox.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Triade-MailScanner-Information: Por favor, entre em contato para obter detalhes X-Triade-MailScanner: Found to be clean X-MailScanner-From: grupos@carvalhaes.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.202 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/200 X-Sequence-Number: 21801 Hi Gnanavel, Thanks for your promptly answer. Yes, your solution solves this problem BUT the point is that I don't wanna a solution that works only if the codes are in desc order. For example, if the codes are on the order above: 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.4, 80); INSERT INTO test (code, description, quant, price, total) VALUES ('99120', 'PRODUCT C', 200, 0.80, 160); INSERT INTO test (code, description, quant, price, total) VALUES ('99120', 'PRODUCT C', 100, 0.9, 90); INSERT INTO test (code, description, quant, price, total) VALUES ('99100', 'PRODUCT A', 10, 1, 10); INSERT INTO test (code, description, quant, price, total) VALUES ('99100', 'PRODUCT A', 5, 0.9, 9); INSERT INTO test (code, description, quant, price, total) VALUES ('99100', 'PRODUCT A', 100, 0.9, 90); INSERT INTO test (code, description, quant, price, total) VALUES ('99100', 'PRODUCT A', 10, 1.10, 11); INSERT INTO test (code, description, quant, price, total) VALUES ('99130', 'PRODUCT b', 10, 1.10, 11); INSERT INTO test (code, description, quant, price, total) VALUES ('99130', 'PRODUCT b', 10, 1.10, 11); INSERT INTO test (code, description, quant, price, total) VALUES ('99130', 'PRODUCT b', 10, 1.10, 11); INSERT INTO test (code, description, quant, price, total) VALUES ('99130', 'PRODUCT b', 20, 0.80, 8); With this data your query result is wrong: dadosadv=# SELECT * FROM (SELECT * FROM product_sales() ) t order by t.code desc, t.subtotal; code | description | quant | price | total | subtotal -------+-------------+-------+-------+-------+---------- 99130 | PRODUCT b | 10 | 1.1 | 11 | 11 99130 | PRODUCT b | 10 | 1.1 | 11 | 22 99130 | PRODUCT b | 10 | 1.1 | 11 | 33 99130 | PRODUCT b | 20 | 0.8 | 8 | 41 99120 | PRODUCT C | 10 | 0.8 | 8 | 8 99120 | PRODUCT C | 100 | 0.8 | 80 | 88 99120 | PRODUCT C | 200 | 0.8 | 160 | 248 99120 | PRODUCT C | 100 | 0.9 | 90 | 338 99100 | PRODUCT A | 10 | 1 | 10 | 10 99100 | PRODUCT A | 5 | 0.9 | 9 | 19 99100 | PRODUCT A | 100 | 0.9 | 90 | 109 99100 | PRODUCT A | 10 | 1.1 | 11 | 120 The point is that I wanna that the output always be ordered by the bigger subtotal groups, indepent of the order of the codes... Do you have any idea how I can do it? Thanks, Rodrigo Carvalhaes Gnanavel Shanmugam wrote: >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@carvalhaes.net >>Sent: Thu, 16 Jun 2005 10:07:15 -0300 >>To: s.gnanavel@inbox.com, pgsql-sql@postgresql.org >>Subject: Re: [SQL] SELECT with sum on groups ORDERING by the subtotals >> >>Hi ! >> >>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 >>$BODY$ >>DECLARE >> tbrow RECORD; >> sbrow subtotal_type; >> >>BEGIN >>sbrow.subtotal := 0; >>FOR tbrow IN >>SELECT code, description, quant, price, total FROM test ORDER BY code >>LOOP >> >>IF sbrow.code = tbrow.code THEN >>sbrow.subtotal := sbrow.subtotal + tbrow.total; >>ELSE >>sbrow.subtotal := tbrow.total; >>END IF; >>sbrow.code := tbrow.code; >>sbrow.description := tbrow.description; >>sbrow.quant := tbrow.quant; >>sbrow.price := tbrow.price; >>sbrow.total := tbrow.total; >> >>RETURN NEXT sbrow; >>END LOOP; >> >> >>RETURN; >> >>END; >>$BODY$ >> LANGUAGE 'plpgsql' VOLATILE; >> >>With this function my output is: >> >>dadosadv=# SELECT * FROM product_sales(); >> code | description | quant | price | total | subtotal >>-------+-------------+-------+-------+-------+---------- >> 92110 | PRODUCT A | 10 | 1 | 10 | 10 >> 92110 | PRODUCT A | 5 | 0.9 | 9 | 19 >> 92110 | PRODUCT A | 100 | 0.9 | 90 | 109 >> 92110 | PRODUCT A | 10 | 1.1 | 11 | 120 >> 92190 | PRODUCT b | 10 | 1.1 | 11 | 11 >> 92190 | PRODUCT b | 10 | 1.1 | 11 | 22 >> 92190 | PRODUCT b | 10 | 1.1 | 11 | 33 >> 92190 | PRODUCT b | 20 | 0.8 | 8 | 41 >> 99120 | PRODUCT C | 10 | 0.8 | 8 | 8 >> 99120 | PRODUCT C | 100 | 0.8 | 80 | 88 >> 99120 | PRODUCT C | 200 | 0.8 | 160 | 248 >> 99120 | PRODUCT C | 100 | 0.9 | 90 | 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 | description | quant | price | total | subtotal >>-------+-------------+-------+-------+-------+---------- >> 99120 | PRODUCT C | 10 | 0.8 | 8 | 8 >> 99120 | PRODUCT C | 100 | 0.8 | 80 | 88 >> 99120 | PRODUCT C | 200 | 0.8 | 160 | 248 >> 99120 | PRODUCT C | 100 | 0.9 | 90 | 338 >> 92110 | PRODUCT A | 10 | 1 | 10 | 10 >> 92110 | PRODUCT A | 5 | 0.9 | 9 | 19 >> 92110 | PRODUCT A | 100 | 0.9 | 90 | 109 >> 92110 | PRODUCT A | 10 | 1.1 | 11 | 120 >> 92190 | PRODUCT b | 10 | 1.1 | 11 | 11 >> 92190 | PRODUCT b | 10 | 1.1 | 11 | 22 >> 92190 | PRODUCT b | 10 | 1.1 | 11 | 33 >> 92190 | PRODUCT b | 20 | 0.8 | 8 | 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 "on before insert" trigger to update the subtotal with sum of >>>total. >>> >>> >>> >>> >>> >>> >>>>-----Original Message----- >>>>From: grupos@carvalhaes.net >>>>Sent: Thu, 16 Jun 2005 00:56:42 -0300 >>>>To: pgsql-sql@postgresql.org >>>>Subject: [SQL] SELECT with sum on groups ORDERING by the subtotals >>>> >>>>Hi Guys! >>>> >>>>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 | description | quant | price | total | subtotal >>>>-------+-------------+-------+-------+-------+---------- >>>>99120 | PRODUCT C | 10 | 0.8 | 8 | 8 >>>>99120 | PRODUCT C | 100 | 0.8 | 80 | 88 >>>>99120 | PRODUCT C | 200 | 0.8 | 160| 168 >>>>99120 | PRODUCT C | 100 | 0.9 | 90 | 667 >>>>92110 | PRODUCT A | 10 | 1 | 10 | 10 >>>>92110 | PRODUCT A | 5 | 0.9 | 9 | 19 >>>>92110 | PRODUCT A | 100 | 0.9 | 90 | 109 >>>>92110 | PRODUCT A | 10 | 1.1 | 11 | 120 >>>>92190 | PRODUCT b | 10 | 1.1 | 11 | 11 >>>>92190 | PRODUCT b | 10 | 1.1 | 11 | 22 >>>>92190 | PRODUCT b | 10 | 1.1 | 11 | 33 >>>>92190 | PRODUCT b | 20 | 0.8 | 8 | 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! >>>> >>>>Rodrigo Carvalhaes >>>> >>>>-- >>>>Esta mensagem foi verificada pelo sistema de antivĂ­rus 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 >> >> >---------------------------(end of broadcast)--------------------------- >TIP 7: don't forget to increase your free space map settings > > > -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo.