Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 percentages and using sum Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, October 13, 2007 12:57 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, October 16, 2007 12:18 AM Points: 7, Visits: 19
 Hi There, I'm trying to sum the values of some rows, subtract them and display them as a percentage.Its for a voting type application where people can vote for and against. If I have 2 presenters and they have 1 votefor each then they each have 50% of the total votes. If someone votes against one of the presenters then the vote against is added to the vote against column and thus the other presenter has 100% of the votes. There are a few things that I'm having issues with 1. where I subtract the two values if I use the / count(*) at the end then the count(*) factors in zeros as well as 1's so if my actual rowcount is 8 (including only 1's) it will show all rows including the ones with a zero 2. I want to show the percentage of overall votes not just the percentage of votes for and against for each presenter. Could someone help fix my query please, thanks in advance !----------- query declare @Gender varchar(20) set @Gender = 'female' select a.PresenterID, a.presenterName, a.PresenterGender, SUM(CASE WHEN isnull(VoteFor,0) = 1 THEN 1 ELSE 0 END) AS NumVoteFor, SUM(CASE WHEN isnull(VoteAgainst,0) = 1 THEN 1 ELSE 0 END) AS NumVoteAgainst, ((SUM(CASE WHEN isnull(VoteFor,0) = 1 THEN 1 ELSE 0 END)) - (SUM(CASE WHEN isnull(VoteAgainst,0) = 1 THEN 1 ELSE 0 END)) ) AS totalVotes 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 !-----------sample output 8Jackiefemale 000 10Meganfemale 000 11Alexandrafemale 312 12Mimifemale 000 14Youshafemale 000 15Angelafemale 514 18Karafemale 000 !------------------------------ data 2115/10/2007 6:40:46 AM10 2115/10/2007 6:40:46 AM01 2155/10/2007 6:40:46 AM10 2155/10/2007 6:40:46 AM01 2155/10/2007 6:40:46 AM10 2155/10/2007 6:40:46 AM10 2155/10/2007 6:40:46 AM10 2155/10/2007 6:40:46 AM10 2115/10/2007 6:40:46 AM10 2115/10/2007 6:40:46 AM10
Post #410269
 Posted Saturday, October 13, 2007 5:55 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 4:42 AM Points: 2,535, Visits: 1,662
Post #410287
 Posted Monday, October 15, 2007 2:12 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, October 16, 2007 12:18 AM Points: 7, Visits: 19
Post #410721
 Posted Monday, October 15, 2007 4:39 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 4:42 AM Points: 2,535, Visits: 1,662
 you should just hav to move your left join outside the derived table (V) as suchSELECTpr2.presenterName,V.PresenterID,V.VoteFor,CAST(V.VoteFor * 1.00 / @TotalVotes as Decimal(3,2)) as VotePercentageFROM (--get votes for presenterSELECTpr2.PresenterID,SUM( CASE WHEN VoteFor = 1 THEN 1 WHEN VoteAgainst = 1 THEN -1 ELSE 0 END) as VotesFROM dbo.tblCompWeek_02 qWHERE pr2.PresenterGender=@GenderGROUP BYq.PresenterID,pr2.PresenterID,pr2.PresenterGender) Vleft outer JOIN dbo.tblPresenters pr2ON v.PresenterID = pr2.PresenterID
Post #410759
 Posted Monday, October 15, 2007 4:51 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 4:42 AM Points: 2,535, Visits: 1,662
 you do not actually need the derived table logic.you could do the math as part of the aggregated select if you wanted as well.I was hoping the derived table just made it more understandable.
Post #410762
 Posted Monday, October 15, 2007 9:13 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, October 16, 2007 12:18 AM Points: 7, Visits: 19
 HI Joel,I tried the suggestions that you offered. I tried to incorporate the @TotalVotes count as a subselect but I keep getting errors. So I came up with the following:I cast the VotePercentage as int because I have to pass it back to a js function in order to display it. Do I need to have to seperate selects in order to display the count @TotalVotes or can incorporate this somehow into the main query?declare @Gender varchar(20)set @Gender = 'female'DECLARE @TotalVotes intSELECT @TotalVotes = SUM(CASE WHEN VoteFor = 1 THEN 1WHEN VoteAgainst = 1 THEN -1ELSE 0END)from dbo.tblPresenters aleft outer JOIN dbo.tblCompWeek_02 prON a.PresenterID = pr.PresenterIDWHERE PresenterGender=@Genderselect a.PresenterID,a.presenterName,a.PresenterGender,SUM(CASE WHEN isnull(VoteFor,0) = 1 THEN 1 ELSE 0 END) AS NumVoteFor,SUM(CASE WHEN isnull(VoteAgainst,0) = 1 THEN 1 ELSE 0 END) AS NumVoteAgainst,cast(SUM(CASE WHEN VoteFor = 1 THEN 1 WHEN VoteAgainst = 1 THEN -1 ELSE 0 END)* 100 / @TotalVotes as int) as VotePercentagefrom dbo.tblPresenters aleft outer JOIN dbo.tblCompWeek_02 prON a.PresenterID = pr.PresenterIDWHERE PresenterGender=@Gendergroup by a.PresenterID,pr.PresenterID,a.presenterName,a.PresenterGender8Jackiefemale 0 0 010Meganfemale 0 0 011Alexandrafemale 3 1 3312Mimifemale 0 0 014Youshafemale 0 0 015Angelafemale 5 1 6618Karafemale 0 0 0
Post #411102

 Permissions