Finding a percentage of a Count

Thread: Finding a percentage of a Count

1. Senior Member
Join Date
Dec 1969
Posts
655

Finding a percentage of a Count

OK I have a table with holds the results of a poll. It holds the answer choosen and the poll number. What I want to do it return the number of answers for result 1 / total number of responses. IE the percentage of people who voted for result 1 and so on. There is at least 2 answers. The closest I could get was <BR><BR>SELECT COUNT (answer) FROM pollResult WHERE poll = 1 UNION <BR>SELECT COUNT (answer) FROM pollResult WHERE poll = 1 AND ANSWER = 1 UNION <BR>SELECT COUNT (answer) FROM pollResult WHERE poll = 1 AND ANSWER = 2<BR><BR>I thought I would then just be able to read the three rows and take care of it in the ASP but if the votes are even it will only return two rows. Does anybody have a better way to do this? DB is Access 2k<BR><BR>Thanks All!

2. Senior Member
Join Date
Dec 1969
Posts
16,931

RE: Finding a percentage of a Count

SELECT (SELECT COUNT (answer) FROM pollResult WHERE poll = 1) As TotalVotes, (SELECT COUNT (answer) FROM pollResult WHERE poll = 1 AND ANSWER = 1) As Answer1Votes, (SELECT COUNT (answer) FROM pollResult WHERE poll = 1 AND ANSWER = 2) As Answer2Votes<BR><BR>That should work - try sticking it in as a "query" first - so you can see what the resultant recordset is like.<BR><BR>Craig.

3. Senior Member
Join Date
Dec 1969
Posts
7,686

small improvement?

SELECT P.PollID,Answer, COUNT(answer) as CountPerAnswer, <BR> COUNT(Answer)/CAST((SELECT COUNT(Answer) as TotalCount FROM Polls WHERE Polls.PollID = P.PollID) as numeric(3,2)) as Percentage<BR>FROM Polls P<BR>GROUP BY P.PollID, Answer<BR><BR>this should work for SQL server, if it&#039;s access use the CONVERT function instead of CAST. (check the docs)<BR><BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
655

Thanks

5. Senior Member
Join Date
Dec 1969
Posts
7,686

huh, but what if

you get three polls or seven answers?

6. Senior Member
Join Date
Dec 1969
Posts
655

RE: huh, but what if

I was going to build the query in ASP based on the number of possible answers and I actually had it built then I saw your solution which is much more flexible and switched to that. Thanks to the both of you though!

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•