October 13, 2007 at 12:57 am
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
October 13, 2007 at 5:55 am
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
October 15, 2007 at 2:12 am
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
October 15, 2007 at 4:39 am
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
October 15, 2007 at 4:51 am
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.
October 15, 2007 at 9:13 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply