Percentages in SQl

[philrm]

Hi there

Do anybody know how to calculate percentage based on stored value eg If beer equals 3 votes and pizza equal 2 votes. how do i display the percentage of votes for beer and for pizza.

 

[VogonPoet]

Pizza and Beer,

Assuming your table has two fields: Item and Votes.

select yt.Item, yt.Votes/t.TotalVotes
from YourTable yt
cross join
(
Select sum(Votes) as TotalVotes
From YourTable
) t

I think this will get you what you need. One caveat, if votes is an integer, you'll need to use a cast function to convert the Votes and TotalVotes to decimals.

 

[williamu]

Hi,

First store your calculations then :

select sum((TotalRows * BeerTotal) / 100), sum((TotalRows * PizzaRows) / 100) ;

So a total of 50 rows, 12 beers and 8 pizza = 6% Beer and 4% Pizza

HTH.





William
Software Engineer
ICQ No. 56047340

 

[bperry]

Both suggestions work fine.
A variation of williamu's suggestion is:

Select Item,
Votes/(Select SUM(VOTES) from Yourtable) as Perc
from Yourtable

 

[rajeessh]

I hope he needs a sum or total in such case the following will be better

select votetype,100 * sum(votes)/(select count(*) from election) from election group by votetype

votetype = beer or x
election = tablename
votes = field that indicates vote


If u need count % make sum in above sql as count

Pl get back to me reg this