Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

percentages and using sum Expand / Collapse
Author
Message
Posted Saturday, October 13, 2007 12:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:14 AM
Points: 1,849, Visits: 1,151
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
Post #410287
Posted Monday, October 15, 2007 2:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Post #410721
Posted Monday, October 15, 2007 4:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:14 AM
Points: 1,849, Visits: 1,151
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
Post #410759
Posted Monday, October 15, 2007 4:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:14 AM
Points: 1,849, Visits: 1,151
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Post #411102
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse