[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 858
  • Last Modified:

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

0
bullrout
Asked:
bullrout
  • 2
1 Solution
 
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
 
AmitChoudhary123Commented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now