

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




SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 6:33 AM
Points: 1,854,
Visits: 1,153


something like this should work for you
DECLARE @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 int
SELECT @TotalVotes = SUM(CASE WHEN VoteFor = 1 THEN 1
WHEN VoteAgainst = 1 THEN 1
ELSE 0
END)
FROM @Votes
SELECT
V.Presenter,
V.Votes,
CAST(V.Votes * 1.00 / @TotalVotes as Decimal(3,2)) as VotePercentage
FROM ( 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 ) V




Forum Newbie
Group: General Forum Members
Last Login: Tuesday, October 16, 2007 12:18 AM
Points: 7,
Visits: 19


Hi 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 / female
The 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 presenters
CREATE 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 int
SELECT @TotalVotes = SUM(CASE WHEN VoteFor = 1 THEN 1
WHEN VoteAgainst = 1 THEN 1
ELSE 0
END)
from dbo.tblPresenters a
left outer JOIN dbo.tblCompWeek_02 pr
ON a.PresenterID = pr.PresenterID
WHERE PresenterGender=@Gender
SELECT
pr2.presenterName,
V.PresenterID,
V.VoteFor,
CAST(V.VoteFor * 1.00 / @TotalVotes as Decimal(3,2)) as VotePercentage
FROM (get votes for presenter
SELECT
pr2.PresenterID,
SUM( CASE WHEN VoteFor = 1 THEN 1 WHEN VoteAgainst = 1 THEN 1 ELSE 0 END) as Votes
FROM dbo.tblCompWeek_02 q
left outer JOIN dbo.tblPresenters pr2
ON q.PresenterID = pr2.PresenterID
WHERE pr2.PresenterGender=@Gender
GROUP BY
q.PresenterID,pr2.PresenterID,pr2.PresenterGender) V




SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 6:33 AM
Points: 1,854,
Visits: 1,153


you should just hav to move your left join outside the derived table (V) as such
SELECT pr2.presenterName, V.PresenterID, V.VoteFor, CAST(V.VoteFor * 1.00 / @TotalVotes as Decimal(3,2)) as VotePercentage FROM (get votes for presenter SELECT pr2.PresenterID, SUM( CASE WHEN VoteFor = 1 THEN 1 WHEN VoteAgainst = 1 THEN 1 ELSE 0 END) as Votes FROM dbo.tblCompWeek_02 q WHERE pr2.PresenterGender=@Gender GROUP BY q.PresenterID,pr2.PresenterID,pr2.PresenterGender) V left outer JOIN dbo.tblPresenters pr2 ON v.PresenterID = pr2.PresenterID




SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 6:33 AM
Points: 1,854,
Visits: 1,153


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.




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 int
SELECT @TotalVotes = SUM(CASE WHEN VoteFor = 1 THEN 1 WHEN VoteAgainst = 1 THEN 1 ELSE 0 END) from dbo.tblPresenters a left outer JOIN dbo.tblCompWeek_02 pr ON a.PresenterID = pr.PresenterID WHERE PresenterGender=@Gender
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,
cast(SUM(CASE WHEN VoteFor = 1 THEN 1 WHEN VoteAgainst = 1 THEN 1 ELSE 0 END)* 100 / @TotalVotes as int) as VotePercentage
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
8Jackiefemale 0 0 0 10Meganfemale 0 0 0 11Alexandrafemale 3 1 33 12Mimifemale 0 0 0 14Youshafemale 0 0 0 15Angelafemale 5 1 66 18Karafemale 0 0 0



