Обсуждение: 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 | 899120 |
PRODUCTC | 100 | 0.8 | 80 | 8899120 | PRODUCT C | 200 | 0.8 | 160| 16899120 | PRODUCT C |
100 | 0.9 | 90 | 66792110 | 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
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 eacredita-se estar livre de perigo.
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.
with regards,
S.Gnanavel
> -----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
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.
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
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.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > I need an subtotal for all the products with the same group and that the > query be ordered by the bigger subtotal. (please proofread: the subtotals in your example output did not add up) By "same group" I presume you mean the same code, as you don't actually use the "group varchar(10)" column you created in your example. A major problem you have is that you have no other way of ordering the rows except by the code. So having a running subtotal is fairly pointless, as the items within each code will appear randomly. Since only the grand total for each code is significant, you could write something like this: SELECT t.*, s.subtotal FROM(SELECT code, SUM(total) AS subtotal FROM test GROUP BY 1) s,test tWHERE s.code = t.codeORDERBY subtotal desc; code | description | quant | price | total | subtotal - -------+-------------+-------+-------+-------+----------99120 | PRODUCT C | 10 | 0.8 | 8 | 33899120 | PRODUCTC | 100 | 0.8 | 80 | 33899120 | PRODUCT C | 200 | 0.8 | 160 | 33899120 | PRODUCT C | 100 | 0.9 | 90 | 33892110 | PRODUCT A | 10 | 1 | 10 | 12092110 | PRODUCT A | 5 | 0.9| 9 | 12092110 | PRODUCT A | 100 | 0.9 | 90 | 12092110 | PRODUCT A | 10 | 1.1 | 11 | 12092190 | PRODUCT b | 10 | 1.1 | 11 | 4192190 | PRODUCT b | 10 | 1.1 | 11 | 4192190| PRODUCT b | 10 | 1.1 | 11 | 4192190 | PRODUCT b | 20 | 0.8 | 8 | 41 If you don't need all that intermediate stuff: SELECT code, SUM(total) AS subtotal FROM test GROUP BY 1 ORDER BY 2 DESC; code | subtotal - -------+----------99120 | 33892110 | 12092190 | 41 If you do need the other rows, you will have to specify a way of ordering the rows within a code group. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200506161458 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFCscxavJuQZxSWSsgRAubaAKDXtwvbX4FyvSMeOYqCWAYfStv3xgCfX+XM 79gJZ2hUgDk1jL3LDQv3le0= =mpnW -----END PGP SIGNATURE-----
Hi Greg, Thanks for your reply. Yes, same group of code... Perfect solution, simple and efficient. Thank you very much!!! Cheers, Rodrigo Carvalhaes Greg Sabino Mullane wrote: >-----BEGIN PGP SIGNED MESSAGE----- >Hash: SHA1 > > > > >>I need an subtotal for all the products with the same group and that the >>query be ordered by the bigger subtotal. >> >> > >(please proofread: the subtotals in your example output did not add up) > >By "same group" I presume you mean the same code, as you don't actually use >the "group varchar(10)" column you created in your example. A major problem >you have is that you have no other way of ordering the rows except by the >code. So having a running subtotal is fairly pointless, as the items within >each code will appear randomly. Since only the grand total for each code is >significant, you could write something like this: > >SELECT t.*, s.subtotal FROM > (SELECT code, SUM(total) AS subtotal FROM test GROUP BY 1) s, > test t > WHERE s.code = t.code > ORDER BY subtotal desc; > > code | description | quant | price | total | subtotal >- -------+-------------+-------+-------+-------+---------- > 99120 | PRODUCT C | 10 | 0.8 | 8 | 338 > 99120 | PRODUCT C | 100 | 0.8 | 80 | 338 > 99120 | PRODUCT C | 200 | 0.8 | 160 | 338 > 99120 | PRODUCT C | 100 | 0.9 | 90 | 338 > 92110 | PRODUCT A | 10 | 1 | 10 | 120 > 92110 | PRODUCT A | 5 | 0.9 | 9 | 120 > 92110 | PRODUCT A | 100 | 0.9 | 90 | 120 > 92110 | PRODUCT A | 10 | 1.1 | 11 | 120 > 92190 | PRODUCT b | 10 | 1.1 | 11 | 41 > 92190 | PRODUCT b | 10 | 1.1 | 11 | 41 > 92190 | PRODUCT b | 10 | 1.1 | 11 | 41 > 92190 | PRODUCT b | 20 | 0.8 | 8 | 41 > >If you don't need all that intermediate stuff: > >SELECT code, SUM(total) AS subtotal FROM test GROUP BY 1 ORDER BY 2 DESC; > > code | subtotal >- -------+---------- > 99120 | 338 > 92110 | 120 > 92190 | 41 > >If you do need the other rows, you will have to specify a way of ordering >the rows within a code group. > >- -- >Greg Sabino Mullane greg@turnstep.com >PGP Key: 0x14964AC8 200506161458 >http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 > >-----BEGIN PGP SIGNATURE----- > >iD8DBQFCscxavJuQZxSWSsgRAubaAKDXtwvbX4FyvSMeOYqCWAYfStv3xgCfX+XM >79gJZ2hUgDk1jL3LDQv3le0= >=mpnW >-----END PGP SIGNATURE----- > > > > > -- Esta mensagem foi verificada pelo sistema de antivírus eacredita-se estar livre de perigo.