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