Message-ID: <42B30532.1040405@carvalhaes.net>
Date: Fri, 17 Jun 2005 14:15:30 -0300
From: grupos <grupos@carvalhaes.net>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: Greg Sabino Mullane <greg@turnstep.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT with sum on groups ORDERING by the subtotals
References: <271d057bf6acfee775e7c47fe899cc9e@biglumber.com>
In-Reply-To: <271d057bf6acfee775e7c47fe899cc9e@biglumber.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.