Re: SELECT with sum on groups ORDERING by the subtotals

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(at)turnstep(dot)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.