From: "Greg Sabino Mullane" <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Cc: grupos@carvalhaes.net
Cc: grupos@carvalhaes.net
Subject: Re: SELECT with sum on groups ORDERING by the subtotals
In-Reply-To: <42B0F87A.2040503@carvalhaes.net>
Date: Thu, 16 Jun 2005 19:01:19 -0000
Message-ID: <271d057bf6acfee775e7c47fe899cc9e@biglumber.com>


-----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-----