Calculating top 20 qty by %

[robbydogg]

Hello,

I am having trouble making a top 20 calculation be the % variance.

the table contains a couple of columns, but columns CY_QTY & PY_QTY are both numerical values

below is my code:

use summary_tables

SELECT    
top 20 
SUM(CY_QTY)- SUM(PY_QTY) AS YTDVAR,
(SUM(CY_QTY)- SUM(PY_QTY))/sum(PY_QTY) AS YTDVARp,
[description],

HPGCODE


FROM         
SUMMARY_HPG_Analysis_Report



GROUP BY 
HPGCODE,
[description]

having SUM(PY_QTY) <>0

order by 
(SUM(CY_QTY)- SUM(PY_QTY))/sum(PY_QTY) asc

which displays the following:

YTDVAR YTDVARp description HPGCODE
-387 -1 AUDIO YF
-4 -1 B2B ADULT HEADWEAR YMD
-15 -1 B2B KEYRINGS YME
-8 -1 B2B LADIES CLOTHING YMB
-1 -1 BODY PARTS/BULKHEADS KAG
3 -1 BRAKE PIPES/SHOES BF
1 -1 BRAKE SHOES BCB

what i need is for column YTDVARp to show a % figure (or a 1.24 etc) so that it can sort by this measure.
I have searched around but can't find anythign that fixes it - can anyone help?


thanks

 

[Menthos]

Hi,

I think you'll get a much better response if you post your problem in a SQL related forum, rather than a VB.net one.