Re: SELECT with sum on groups ORDERING by the subtotals

Hi Gnanavel,

Thanks for your promptly answer. Yes, your solution solves this problem 
BUT the point is that I don't wanna a solution that works only if the 
codes are in desc order.
For example, if the codes are on the order above:

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.4, 80);
INSERT INTO test (code, description, quant, price, total) VALUES 
('99120', 'PRODUCT C', 200, 0.80, 160);
INSERT INTO test (code, description, quant, price, total) VALUES 
('99120', 'PRODUCT C', 100, 0.9, 90);
INSERT INTO test (code, description, quant, price, total) VALUES 
('99100', 'PRODUCT A', 10, 1, 10);
INSERT INTO test (code, description, quant, price, total) VALUES 
('99100', 'PRODUCT A', 5, 0.9, 9);
INSERT INTO test (code, description, quant, price, total) VALUES 
('99100', 'PRODUCT A', 100, 0.9, 90);
INSERT INTO test (code, description, quant, price, total) VALUES 
('99100', 'PRODUCT A', 10, 1.10, 11);
INSERT INTO test (code, description, quant, price, total) VALUES 
('99130', 'PRODUCT b', 10, 1.10, 11);
INSERT INTO test (code, description, quant, price, total) VALUES 
('99130', 'PRODUCT b', 10, 1.10, 11);
INSERT INTO test (code, description, quant, price, total) VALUES 
('99130', 'PRODUCT b', 10, 1.10, 11);
INSERT INTO test (code, description, quant, price, total) VALUES 
('99130', 'PRODUCT b', 20, 0.80, 8);


With this data your query result is wrong:

dadosadv=# SELECT * FROM (SELECT * FROM product_sales() ) t order by 
t.code desc, t.subtotal;code  | description | quant | price | total | subtotal
-------+-------------+-------+-------+-------+----------99130 | PRODUCT b   |    10 |   1.1 |    11 |       1199130 |
PRODUCTb   |    10 |   1.1 |    11 |       2299130 | PRODUCT b   |    10 |   1.1 |    11 |       3399130 | PRODUCT b
|   20 |   0.8 |     8 |       4199120 | PRODUCT C   |    10 |   0.8 |     8 |        899120 | PRODUCT C   |   100 |
0.8|    80 |       8899120 | PRODUCT C   |   200 |   0.8 |   160 |      24899120 | PRODUCT C   |   100 |   0.9 |    90
|     33899100 | PRODUCT A   |    10 |     1 |    10 |       1099100 | PRODUCT A   |     5 |   0.9 |     9 |
1999100| PRODUCT A   |   100 |   0.9 |    90 |      10999100 | PRODUCT A   |    10 |   1.1 |    11 |      120
 


The point is that I wanna that the output always be ordered by the 
bigger subtotal groups, indepent of the order of the codes...
Do you have any idea how I can do it?

Thanks,

Rodrigo Carvalhaes

Gnanavel Shanmugam wrote:

>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
>>    
>>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>  
>

-- 
Esta mensagem foi verificada pelo sistema de antiv�rus eacredita-se estar livre de perigo.