Re: SELECT with sum on groups ORDERING by the subtotals - Mailing list pgsql-sql
From | Gnanavel Shanmugam |
---|---|
Subject | Re: SELECT with sum on groups ORDERING by the subtotals |
Date | |
Msg-id | FFF911D137A.0000060As.gnanavel@inbox.com Whole thread Raw |
In response to | SELECT with sum on groups ORDERING by the subtotals (grupos <grupos@carvalhaes.net>) |
List | pgsql-sql |
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. with regards, S.Gnanavel > -----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