Mime-Version: 1.0
Date: Wed, 15 Jun 2005 20:25:55 -0800
Message-ID: <FFF911D137A.0000060As.gnanavel@inbox.com>
From: Gnanavel Shanmugam <s.gnanavel@inbox.com>
Subject: Re: SELECT with sum on groups ORDERING by the subtotals
To: pgsql-sql@postgresql.org
In-Reply-To: <42B0F87A.2040503@carvalhaes.net>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

I think it will be better to add one more column for subtotal and
write an =22on before insert=22 trigger to update the subtotal with sum of
total.


with regards,
S.Gnanavel


> -----Original Message-----
> From: grupos=40carvalhaes.net
> Sent: Thu, 16 Jun 2005 00:56:42 -0300
> To: pgsql-sql=40postgresql.org
> Subject: =5BSQL=5D SELECT with sum on groups ORDERING by the subtotals
>
> Hi Guys=21
>
> 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  =7C description =7C quant =7C price =7C total =7C subtotal
> -------+-------------+-------+-------+-------+----------
>  99120 =7C PRODUCT C   =7C    10 =7C   0.8  =7C     8  =7C      8
>  99120 =7C PRODUCT C   =7C   100 =7C   0.8 =7C    80 =7C      88
>  99120 =7C PRODUCT C   =7C   200 =7C   0.8 =7C   160=7C      168
>  99120 =7C PRODUCT C   =7C   100 =7C   0.9 =7C    90 =7C      667
>  92110 =7C PRODUCT A   =7C    10 =7C     1   =7C    10 =7C       10
>  92110 =7C PRODUCT A   =7C     5 =7C   0.9   =7C     9  =7C       19
>  92110 =7C PRODUCT A   =7C   100 =7C   0.9 =7C    90 =7C      109
>  92110 =7C PRODUCT A   =7C    10 =7C   1.1  =7C    11 =7C      120
>  92190 =7C PRODUCT b   =7C    10 =7C   1.1   =7C    11 =7C      11
>  92190 =7C PRODUCT b   =7C    10 =7C   1.1   =7C    11 =7C      22
>  92190 =7C PRODUCT b   =7C    10 =7C   1.1   =7C    11 =7C      33
>  92190 =7C PRODUCT b   =7C    20 =7C   0.8   =7C     8  =7C      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=21
>
> Rodrigo Carvalhaes
>
> --
> Esta mensagem foi verificada pelo sistema de antiv=C3=ADrus e
>  acredita-se estar livre de perigo.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings=