Calculate Percentage

[jgoodman00]

I have the following query results & I need to calculate the perct column based on the total for a type (e.g. Bramble).
Type Priority Count Perct
Bramble A 25 62.5
Bramble B 15 37.5


Any suggestions?

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[dbomrrsm]

can you post the query as it is now ?

[blue]DBomrrsm[/blue]

 

[donutman]

I've made a couple of assumptions about your data, but I think this will do.

[Blue]SELECT[/Blue] M1.Type[Gray],[/Gray] Priority[Gray],[/Gray] [Fuchsia]SUM[/Fuchsia][Gray]([/Gray]Quantity[Gray])[/Gray] SubTotal[Gray],[/Gray] 
   [Blue]CASE[/Blue] [Blue]WHEN[/Blue] TotalQty[Gray]>[/Gray]0 [Blue]THEN[/Blue] SubTotal/TotalQty 
        [Blue]ELSE[/Blue] 0 [Blue]END[/Blue] Perct 
   [Blue]FROM[/Blue] MyTable M1 [Blue]GROUP[/Blue] [Blue]BY[/Blue] Type[Gray],[/Gray] Priority [Blue]INNER[/Blue] [Blue]JOIN[/Blue] 
      [Gray]([/Gray][Blue]SELECT[/Blue] Type[Gray],[/Gray] [Fuchsia]SUM[/Fuchsia][Gray]([/Gray]Quantity[Gray])[/Gray] TotalQty 
       [Blue]FROM[/Blue] MyTable [Blue]GROUP[/Blue] [Blue]BY[/Blue] Type[Gray])[/Gray] M2
   [Blue]ON[/Blue] M1.Type[Gray]=[/Gray]M2.Type

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jgoodman00]

Thanks. That jolted my memory enough so I could do it...

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com