Message-ID: <42B0F87A.2040503@carvalhaes.net>
Date: Thu, 16 Jun 2005 00:56:42 -0300
From: grupos <grupos@carvalhaes.net>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: SELECT with sum on groups ORDERING by the subtotals
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.