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
