Re: SELECT with sum on groups ORDERING by the subtotals - Mailing list pgsql-sql

This might work,
select * from (SELECT * FROM product_sales()) t order by t.code
desc,t.subtotal;

with regards,
S.Gnanavel


> -----Original Message-----
> From: grupos@carvalhaes.net
> Sent: Thu, 16 Jun 2005 10:07:15 -0300
> To: s.gnanavel@inbox.com, pgsql-sql@postgresql.org
> Subject: Re: [SQL] SELECT with sum on groups ORDERING by the subtotals
>
> 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 |       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
>  99120 | PRODUCT C   |    10 |   0.8 |     8 |        8
>  99120 | PRODUCT C   |   100 |   0.8 |    80 |       88
>  99120 | PRODUCT C   |   200 |   0.8 |   160 |      248
>  99120 | 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 |        8
>  99120 | PRODUCT C   |   100 |   0.8 |    80 |       88
>  99120 | PRODUCT C   |   200 |   0.8 |   160 |      248
>  99120 | PRODUCT C   |   100 |   0.9 |    90 |      338
>  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
>
> 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 antivrus e
>  acredita-se estar livre de perigo.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq