SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


percentages and using sum


percentages and using sum

Author
Message
sean-165363
sean-165363
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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
Joel Ewald
Joel Ewald
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3926 Visits: 1821
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
sean-165363
sean-165363
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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
Joel Ewald
Joel Ewald
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3926 Visits: 1821
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
Joel Ewald
Joel Ewald
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3926 Visits: 1821
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.
sean-165363
sean-165363
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search