Re: [SQL] SELECT with sum on groups ORDERING by the subtotals

grupos Thu, 16 Jun 2005 06:11:51 -0700

Hi !

This is not possible because the query will have a time interval and thesubtotal will change due the intervals passed to the query...To get the subtotal I already know how to do it (see below) but theproblem is get the correct output, ordering by the bigger totalsagrouped 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 themax(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: [EMAIL PROTECTED]
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 antiv�s e
acredita-se estar livre de perigo.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

Re: [SQL] SELECT with sum on groups ORDERING by the subtotals

Reply via email to