-----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 [EMAIL PROTECTED] PGP Key: 0x14964AC8 200506161458 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFCscxavJuQZxSWSsgRAubaAKDXtwvbX4FyvSMeOYqCWAYfStv3xgCfX+XM 79gJZ2hUgDk1jL3LDQv3le0= =mpnW -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
