Re: SELECT with sum on groups ORDERING by the subtotals

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(at)carvalhaes(dot)net
> Sent: Thu, 16 Jun 2005 00:56:42 -0300
> To: pgsql-sql(at)postgresql(dot)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