SELECT with sum on groups ORDERING by the subtotals - Mailing list pgsql-sql
From | grupos |
---|---|
Subject | SELECT with sum on groups ORDERING by the subtotals |
Date | |
Msg-id | 42B0F87A.2040503@carvalhaes.net Whole thread Raw |
Responses |
Re: SELECT with sum on groups ORDERING by the subtotals
Re: SELECT with sum on groups ORDERING by the subtotals |
List | pgsql-sql |
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 | 899120 | PRODUCTC | 100 | 0.8 | 80 | 8899120 | PRODUCT C | 200 | 0.8 | 160| 16899120 | PRODUCT C | 100 | 0.9 | 90 | 66792110 | 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 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 eacredita-se estar livre de perigo.