Re: SELECT with sum on groups ORDERING by the subtotals
От | grupos |
---|---|
Тема | Re: SELECT with sum on groups ORDERING by the subtotals |
Дата | |
Msg-id | 42B17983.20606@carvalhaes.net обсуждение исходный текст |
Ответ на | SELECT with sum on groups ORDERING by the subtotals (grupos <grupos@carvalhaes.net>) |
Ответы |
Re: SELECT with sum on groups ORDERING by the subtotals
(Gnanavel Shanmugam <s.gnanavel@inbox.com>)
|
Список | pgsql-sql |
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 | 1092110 | PRODUCTA | 5 | 0.9 | 9 | 1992110 | PRODUCT A | 100 | 0.9 | 90 | 10992110 | PRODUCT A | 10 | 1.1 | 11 | 12092190 | PRODUCT b | 10 | 1.1 | 11 | 1192190 | PRODUCT b | 10 | 1.1| 11 | 2292190 | PRODUCT b | 10 | 1.1 | 11 | 3392190 | PRODUCT b | 20 | 0.8 | 8 | 4199120 | PRODUCT C | 10 | 0.8 | 8 | 899120 | PRODUCT C | 100 | 0.8 | 80 | 8899120| PRODUCT C | 200 | 0.8 | 160 | 24899120 | 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 | 899120 | PRODUCTC | 100 | 0.8 | 80 | 8899120 | PRODUCT C | 200 | 0.8 | 160 | 24899120 | PRODUCT C | 100 | 0.9 | 90 | 33892110 | PRODUCT A | 10 | 1 | 10 | 1092110 | PRODUCT A | 5 | 0.9| 9 | 1992110 | PRODUCT A | 100 | 0.9 | 90 | 10992110 | PRODUCT A | 10 | 1.1 | 11 | 12092190 | PRODUCT b | 10 | 1.1 | 11 | 1192190 | PRODUCT b | 10 | 1.1 | 11 | 2292190| PRODUCT b | 10 | 1.1 | 11 | 3392190 | 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 antiv�rus eacredita-se estar livre de perigo.
В списке pgsql-sql по дате отправления: