InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 12:37:44 GMT20http://www.sqlservercentral.com/Forums/Topic410269-338-1.aspxHI 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 0Mon, 15 Oct 2007 21:13:50 GMTsean-165363http://www.sqlservercentral.com/Forums/Topic410269-338-1.aspxyou 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.Mon, 15 Oct 2007 04:51:33 GMTJoel Ewaldhttp://www.sqlservercentral.com/Forums/Topic410269-338-1.aspxyou 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.PresenterIDMon, 15 Oct 2007 04:39:13 GMTJoel Ewaldhttp://www.sqlservercentral.com/Forums/Topic410269-338-1.aspxHi Joel,Thank you for the answer. I'm getting a little confused with the logic and I was wondering if you could help me finish it off please.I used the left outer join on the query for two reasons:1. I needed the presenterName, presenterGender,PresenterID to display the details even if they had zero votes.2. I needed to distinguish between the genders male / femaleThe top part of the query that tallys the votes is working ok but I need a little help with the second half if you have the time.Sean thanks in advance!---------table presentersCREATE TABLE [dbo].[tblPresenters]([presenterID] [int] IDENTITY(1,1) NOT NULL,[presenterName] [varchar](150) COLLATE Latin1_General_CI_AS NULL,[presenterProfile] [text] COLLATE Latin1_General_CI_AS NULL,[presenterGender] [nchar](10) COLLATE Latin1_General_CI_AS NULL,[presenterAttribution] [varchar](250) COLLATE Latin1_General_CI_AS NULL,[presenterCreated] [datetime] NULL,[presenterLargeImage] [varchar](250) COLLATE Latin1_General_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]!------ table with votes!--------------------------------------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=@GenderSELECTpr2.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 qleft outer JOIN dbo.tblPresenters pr2ON q.PresenterID = pr2.PresenterIDWHERE pr2.PresenterGender=@GenderGROUP BYq.PresenterID,pr2.PresenterID,pr2.PresenterGender) VMon, 15 Oct 2007 02:12:38 GMTsean-165363http://www.sqlservercentral.com/Forums/Topic410269-338-1.aspxsomething like this should work for youDECLARE @Votes table ( VoteId int IDENTITY(1,1) NOT NULL, Presenter char(3)NOT NULL, VoteFor bit NOT NULL DEFAULT (0), VoteAgainst bit NOT NULL DEFAULT (0) )INSERT INTO @Votes VALUES ( 'JIM', 1, 0 ) INSERT INTO @Votes VALUES ( 'JIM', 0, 1 ) INSERT INTO @Votes VALUES ( 'BOB', 1, 0 )DECLARE @TotalVotes intSELECT @TotalVotes = SUM(CASE WHEN VoteFor = 1 THEN 1 WHEN VoteAgainst = 1 THEN -1 ELSE 0 END)FROM @VotesSELECT V.Presenter, V.Votes, CAST(V.Votes * 1.00 / @TotalVotes as Decimal(3,2)) as VotePercentageFROM ( --get votes for presenter SELECT Presenter, SUM( CASE WHEN VoteFor = 1 THEN 1 WHEN VoteAgainst = 1 THEN -1 ELSE 0 END) as Votes FROM @Votes GROUP BY Presenter ) VSat, 13 Oct 2007 05:55:16 GMTJoel Ewaldhttp://www.sqlservercentral.com/Forums/Topic410269-338-1.aspxHi 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 with1. 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 zero2. 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!----------- querydeclare @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 aleft outer JOIN dbo.tblCompWeek_02 prON a.PresenterID = pr.PresenterIDWHERE PresenterGender=@Gendergroup by a.PresenterID,pr.PresenterID,a.presenterName,a.PresenterGender!-----------sample output8Jackiefemale 00010Meganfemale 00011Alexandrafemale 31212Mimifemale 00014Youshafemale 00015Angelafemale 51418Karafemale 000!------------------------------ data2115/10/2007 6:40:46 AM102115/10/2007 6:40:46 AM012155/10/2007 6:40:46 AM102155/10/2007 6:40:46 AM012155/10/2007 6:40:46 AM102155/10/2007 6:40:46 AM102155/10/2007 6:40:46 AM102155/10/2007 6:40:46 AM102115/10/2007 6:40:46 AM102115/10/2007 6:40:46 AM10Sat, 13 Oct 2007 00:57:11 GMTsean-165363