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

LVL 5
bullroutAsked:
Who is Participating?
 
AmitChoudhary123Connect With a Mentor Commented:
yes u need to  add the VoteAgainst value in. as VoteFor/ (VoteFor + VoteAgainst)
=>  1/(1+3) * 100  = 25
=> 3/(1+3)*100  = 75  
take this example..

create table #temp
(
id INT Identity,
vote bit,
voteagainst bit
)
insert #temp
Select 1,0
insert #temp
Select 0,1
insert #temp
Select 0,1
insert #temp
Select 0,1
insert #temp
Select 1,0
insert #temp
Select 0,1
insert #temp
Select 0,1
insert #temp
Select 0,1

---get percentage as
select  Vote = CONVERT(FLOAT,SUM(CASE WHEN  vote = 1 THEN 1.0 ELSE 0 END) /COUNT(CASE WHEN  vote = 1 THEN 1 ELSE 0 END)),
      voteagainst = CONVERT(FLOAT,SUM(CASE WHEN  voteagainst = 1 THEN 1.0 ELSE 0 END) /COUNT(CASE WHEN  voteagainst = 1 THEN 1 ELSE 0 END))
FROM #temp


0
 
AmitChoudhary123Commented:
Hi ,

what u can do is divide the sum of (VoteFor + VoteAgainst) to each part as  
VoteFor/ (VoteFor + VoteAgainst)
and VoteAgainst / (VoteFor + VoteAgainst)
and muliply it to 100 to get req. result.

Hope this would help u..
0
 
bullroutAuthor Commented:
HI There,

Do I need to add the VoteAgainst value in? I have the correct numbers already:

1 voteFor presenterID 11
3 votesFor presenterID 15

everything else is fine, I just need to show the output I already have as 25, 75 respectively.  If I apply * 100 I get 100 and 300.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.