pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT with sum on groups ORDERING by the subtotals
7+ messages / 3 participants
[nested] [flat]
* SELECT with sum on groups ORDERING by the subtotals
@ 2005-06-16 03:56 grupos <grupos@carvalhaes.net>
2005-06-16 04:25 ` Re: SELECT with sum on groups ORDERING by the subtotals Gnanavel Shanmugam <s.gnanavel@inbox.com>
2005-06-16 19:01 ` Re: SELECT with sum on groups ORDERING by the subtotals Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 2 replies; 7+ messages in thread
From: grupos @ 2005-06-16 03:56 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT with sum on groups ORDERING by the subtotals
2005-06-16 03:56 SELECT with sum on groups ORDERING by the subtotals grupos <grupos@carvalhaes.net>
@ 2005-06-16 04:25 ` Gnanavel Shanmugam <s.gnanavel@inbox.com>
1 sibling, 0 replies; 7+ messages in thread
From: Gnanavel Shanmugam @ 2005-06-16 04:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT with sum on groups ORDERING by the subtotals
2005-06-16 03:56 SELECT with sum on groups ORDERING by the subtotals grupos <grupos@carvalhaes.net>
@ 2005-06-16 19:01 ` Greg Sabino Mullane <greg@turnstep.com>
2005-06-17 17:15 ` Re: SELECT with sum on groups ORDERING by the subtotals grupos <grupos@carvalhaes.net>
1 sibling, 1 reply; 7+ messages in thread
From: Greg Sabino Mullane @ 2005-06-16 19:01 UTC (permalink / raw)
To: pgsql-sql; +Cc: grupos@carvalhaes.net
-----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-----
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT with sum on groups ORDERING by the subtotals
2005-06-16 03:56 SELECT with sum on groups ORDERING by the subtotals grupos <grupos@carvalhaes.net>
2005-06-16 19:01 ` Re: SELECT with sum on groups ORDERING by the subtotals Greg Sabino Mullane <greg@turnstep.com>
@ 2005-06-17 17:15 ` grupos <grupos@carvalhaes.net>
0 siblings, 0 replies; 7+ messages in thread
From: grupos @ 2005-06-17 17:15 UTC (permalink / raw)
To: Greg Sabino Mullane <greg@turnstep.com>; +Cc: pgsql-sql
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 e
acredita-se estar livre de perigo.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT with sum on groups ORDERING by the subtotals
@ 2005-06-16 13:07 grupos <grupos@carvalhaes.net>
0 siblings, 0 replies; 7+ messages in thread
From: grupos @ 2005-06-16 13:07 UTC (permalink / raw)
To: Gnanavel Shanmugam <s.gnanavel@inbox.com>; pgsql-sql
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 antivírus e
acredita-se estar livre de perigo.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT with sum on groups ORDERING by the subtotals
@ 2005-06-16 13:28 Gnanavel Shanmugam <s.gnanavel@inbox.com>
2005-06-16 15:10 ` Re: SELECT with sum on groups ORDERING by the subtotals grupos <grupos@carvalhaes.net>
0 siblings, 1 reply; 7+ messages in thread
From: Gnanavel Shanmugam @ 2005-06-16 13:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT with sum on groups ORDERING by the subtotals
2005-06-16 13:28 Re: SELECT with sum on groups ORDERING by the subtotals Gnanavel Shanmugam <s.gnanavel@inbox.com>
@ 2005-06-16 15:10 ` grupos <grupos@carvalhaes.net>
0 siblings, 0 replies; 7+ messages in thread
From: grupos @ 2005-06-16 15:10 UTC (permalink / raw)
To: Gnanavel Shanmugam <s.gnanavel@inbox.com>; +Cc: pgsql-sql
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 | 11
99130 | PRODUCT b | 10 | 1.1 | 11 | 22
99130 | PRODUCT b | 10 | 1.1 | 11 | 33
99130 | 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
99100 | PRODUCT A | 10 | 1 | 10 | 10
99100 | PRODUCT A | 5 | 0.9 | 9 | 19
99100 | PRODUCT A | 100 | 0.9 | 90 | 109
99100 | 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 e
acredita-se estar livre de perigo.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-06-17 17:15 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-16 03:56 SELECT with sum on groups ORDERING by the subtotals grupos <grupos@carvalhaes.net>
2005-06-16 04:25 ` Gnanavel Shanmugam <s.gnanavel@inbox.com>
2005-06-16 19:01 ` Greg Sabino Mullane <greg@turnstep.com>
2005-06-17 17:15 ` grupos <grupos@carvalhaes.net>
2005-06-16 13:07 Re: SELECT with sum on groups ORDERING by the subtotals grupos <grupos@carvalhaes.net>
2005-06-16 13:28 Re: SELECT with sum on groups ORDERING by the subtotals Gnanavel Shanmugam <s.gnanavel@inbox.com>
2005-06-16 15:10 ` grupos <grupos@carvalhaes.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox