Calculating Percentages - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calculating Percentages

Author

Topic

NeilC
Yak Posting Veteran

55 Posts

Posted - 2008-02-18 : 10:03:43

I have a survey results table:

AnswerID, QuestionID, SurveyID
1, 2, 1
2, 1, 1
3, 2, 1
4, 3, 1
5, 2, 1

The questionID is a FK to a question table so there are multiple records stored in this table per survey. I am trying to write a query to calculate the percentage of votes for each survey.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-18 : 10:17:12

Not sure how you want the output to be. Here is one try:

Declare @T table
(
	AnswerID int, 
	QuestionID int, 
	SurveyID int
)

insert @t
select 1, 2, 1 union all
select 2, 1, 1 union all
select 3, 2, 2 union all
select 4, 3, 1 union all
select 5, 1, 3

select surveyID, (count(QuestionID)*1.0/t2.total)*100.00 as cnt
from @t t1
cross join
(select count(questionid) as total from @t) t2
group by surveyid, t2.total

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-18 : 10:17:56

Try:


Select S.SurveyID, QuestionID, (Cast(Count(*) as real) / TotalPerSurvey) * 100 As PercentOfResults
From SurveyTable S
Inner Join
(
	Select SurveyID, Count(*) TotalPerSurvey
	From SurveyTable
	Group By SurveyID
) TPS On S.SurveyID = TPS.SurveyID
Group By S.SurveyID, QuestionID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources