summing the value of a sub select as a percentage

HI There,

Everthing else is correct in my query except I cannot convert the values into a a percentage. >'
(select cast(sum(VoteFor) AS DECIMAL(16,2))  from dbo.tblCompWeek_02 where PresenterID=a.PresenterID)' this part of my query is a partially correct as it returns to me the number of votesFor per presenter.For example the data below shows that the total amount of votesFor is:

1 for presenter 11
3 for presenter 15

I need this to show:

25 for presenter 11

75 for presenter 15

without affecting  the rest of the query, can someone fix my code please?

Sean - thanks in advance



I have tried everyway to sum  to a percentage in a subselect.I'm just wanting to return the value as 75 or 25 etc

-------------------- query

declare @Gender varchar(20)

set @Gender = 'female'

SELECT PresenterID, presenterName, PresenterGender,
ISNULL(VoteFor,0) AS VoteFor,
ISNULL(VoteAgainst,0) as VoteAgainst,
case when ISNULL(VoteFor,0) <> 0 and ISNULL(VoteAgainst,0) <> 0 then

(select cast(sum(VoteFor) AS DECIMAL(16,2))  from dbo.tblCompWeek_02 where PresenterID=a.PresenterID)

else 0
end as LeaderPercentage
FROM (
select a.PresenterID,
a.presenterName,
a.PresenterGender,
CAST(CAST(SUM(CASE WHEN pr.VoteFor = 1 THEN 1 WHEN pr.VoteFor = 0 THEN 0 END)
AS DECIMAL(16,2)) / CAST(count(*) AS DECIMAL(16,2)) * 100 AS INT) as VoteFor,

CAST(CAST(SUM(CASE WHEN pr.VoteAgainst = 1 THEN 1 WHEN pr.VoteAgainst = 0 THEN 0 END)
AS DECIMAL(16,2)) / CAST(count(*) AS DECIMAL(16,2)) * 100 AS INT) as VoteAgainst

from dbo.tblPresenters a
left outer JOIN dbo.tblCompWeek_02 pr
ON a.PresenterID = pr.PresenterID
WHERE PresenterGender=@Gender
group by a.PresenterID,pr.PresenterID,a.presenterName,a.PresenterGender) AS A



------ sample data


2      11      5/10/2007 6:40:46 AM      1      0
2      11      5/10/2007 6:40:46 AM      0      1
2      15      5/10/2007 6:40:46 AM      1      0
2      15      5/10/2007 6:40:46 AM      0      1
2      15      5/10/2007 6:40:46 AM      1      0
2      15      5/10/2007 6:40:46 AM      1      0


----------------- results



8      Jackie      female          0      0      0.00
10      Megan      female          0      0      0.00
11      Alexandra      female          50      50      1.00
12      Mimi      female          0      0      0.00
14      Yousha      female          0      0      0.00
15      Angela      female          75      25      3.00
18      Kara      female          0      0      0.00