Re: 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 |       1092110 |
PRODUCTA   |     5 |   0.9 |     9 |       1992110 | PRODUCT A   |   100 |   0.9 |    90 |      10992110 | PRODUCT A
|   10 |   1.1 |    11 |      12092190 | PRODUCT b   |    10 |   1.1 |    11 |       1192190 | PRODUCT b   |    10 |
1.1|    11 |       2292190 | PRODUCT b   |    10 |   1.1 |    11 |       3392190 | 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 |      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 |        899120 |
PRODUCTC   |   100 |   0.8 |    80 |       8899120 | PRODUCT C   |   200 |   0.8 |   160 |      24899120 | PRODUCT C
|  100 |   0.9 |    90 |      33892110 | PRODUCT A   |    10 |     1 |    10 |       1092110 | PRODUCT A   |     5 |
0.9|     9 |       1992110 | PRODUCT A   |   100 |   0.9 |    90 |      10992110 | PRODUCT A   |    10 |   1.1 |    11
|     12092190 | PRODUCT b   |    10 |   1.1 |    11 |       1192190 | PRODUCT b   |    10 |   1.1 |    11 |
2292190| PRODUCT b   |    10 |   1.1 |    11 |       3392190 | 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 antiv�rus eacredita-se estar livre de perigo.