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 | 120The 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: [EMAIL PROTECTED] Sent: Thu, 16 Jun 2005 10:07:15 -0300 To: [EMAIL PROTECTED], 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: [EMAIL PROTECTED] 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 thatthequery 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 andputthe 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 ofbroadcast)---------------------------TIP 7: don't forget to increase your free space map settingswith 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�s e
acredita-se estar livre de perigo.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
