Percent of total Table value

[jojo79]

I am a little confused in which way to handle this, I have a table called tbl_inv. It has 3 columns in it.
inv_skew
inv_skids
inv_date
I want to run a query and find out what percent of that skew is of the total value.
Example:
Lets just say there are 20 skews and that there are a total of 100 skids[sum(inv_skids)]. I want to find out what percent of the 100 skids are skew number: ZZZWER.
Skew:ZZZWER
Skid: 45
Percent: 45%
I'm not sure if I need to use the case command or a sub query, or what. Thank you in advance.

 

[SQLDenis]

untested

select inv_skids,count(inv_skids),
(count(inv_skids)/(select count(inv_skids) from table1) * 100.0) as Percentage
from table1
group by inv_skids

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[UNCMoochie]

just a suggestions, you may have to cast/convert the count as a float if you want the percentage. I've had some problems converting counts to percentages with that in the past.

cast(count(inv_skids) as float)/(select cast(count(inv_skids)) as float) from table1)

 

[SQLDenis]

>>just a suggestions, you may have to cast/convert the count as a float if you want the percentage

you don't need to if you do * 100.00 (change that in the query I gave * 100.0 instead of 100.00)

look at this

select (5/3)*100
select (5/3) *100.00

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

UNCMoochie,

Denis's code works because of SQL Server's Data Type Precedence. When using an operator, the data type of lower precedence will be converted to the data type with higher precedence. The complete list of data type precedence can be found here...

http://msdn2.microsoft.com/en-us/library/aa258264(SQL.80).aspx

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[UNCMoochie]

gotcha, my apologies.

 

[jojo79]

I have tried the select statement and all I receive is all zero's, I had to update the statement with columns names and a different table. I thought maybe it might because I have to convert, but that is still not working, below is what I have, I get 0's for both.

Not working.
cast( round( case wh_skid when 0 then 0 else 100 * convert(decimal(9,2), (wh_skid/totalskid)) end, 0 ) as int )

Not working either.
select wh_skid/(select sum(wh_skid) from ipc_warehouse) from ipc_warehouse

 

[jojo79]

OK i got it to work, One more problem.

Current Statement hat Works.
str(cast(100*wh_skid/(select sum(wh_skid) from ipc_warehouse) as decimal(8,2)))+'%'

It returns the following.

17%
5%
0%
0%
6%

I need to see a couple more decimal places. Because the the ones that say 0% are probably 0.45% or something like that.

I need something like this

17.00%
5.00%
0.45%
0.34%
6.45%

Any help would be great. Thanks in advance.