This is very common requirement to generate a
poll result in SQL Server. To get the users opinion against any topic or
subject, we generally create online polls.
To generate a poll grid or poll results, we
store the user input into the database against any poll. We can take a common
example to store poll information and user responses into the database as given
below:
In Poll Master Table, we have the following data
–
Pid
|
Question
|
1
|
Who is the Bollywood Star?
|
2
|
How many days in a year?
|
3
|
Which number does come before 18?
|
In Poll Option Table, we could have the multiple
options against any specific poll/question as given below –
Pid
|
Oid
|
Options
|
1
|
1
|
Virat Kohli
|
1
|
2
|
Rahul Gandhi
|
1
|
3
|
Zac Effron
|
1
|
4
|
Aamir Khan
|
In User Response Table, a single user can choose
only one option against any poll/question and he/she can vote once a while
against any poll/question. This table can contain the following data as given
below –
Usid
|
Pid
|
Oid
|
1
|
1
|
1
|
1
|
2
|
1
|
2
|
1
|
2
|
2
|
2
|
2
|
Logic to calculate the options percentage
against any poll/question:
We have to calculate the followings –
1. Total Responses = count
every response against any Poll question
2. Option Response =count
option response within the same poll question
3. Percentage = (Option
Response/ Total Responses) x 100
Query at a
Glance
---- Poll Master Data
;with
Poll_Master as
( select Pid=1,
Question='Who
is the Bollywood Star?'
UNION select Pid=2, Question='How many days in a year?'
UNION select Pid=3, Question='Which number does come before 18?'
),
---- Poll Options data
Poll_Options as
( select Pid=1,
Oid=1, Options='Virat Kohli'
union select Pid=1, Oid=2,
Options='Rahul
Gandhi'
union select Pid=1, Oid=3,
Options='Zac
Effron'
union select Pid=1, Oid=4,
Options='Aamir
Khan'
union select Pid=2, Oid=1,
Options='367'
union select Pid=2, Oid=2,
Options='356'
union select Pid=2, Oid=3,
Options='363'
union select Pid=2, Oid=4,
Options='365'
union select Pid=3, Oid=1,
Options='16'
union select Pid=3, Oid=2,
Options='15'
union select Pid=3, Oid=3,
Options='17'
union select Pid=3, Oid=4,
Options='19'
),
---- User responses against poll options
User_Response as
(
select Usid=1, Pid=1,
Oid=1
union select Usid=2, Pid=1,
Oid=2
union select Usid=3, Pid=1,
Oid=3
union select Usid=6, Pid=1,
Oid=4
union select Usid=1, Pid=2,
Oid=1
union select Usid=2, Pid=2,
Oid=2
union select Usid=3, Pid=2,
Oid=3
union select Usid=7, Pid=2,
Oid=4
union select Usid=5, Pid=1,
Oid=4
union select Usid=6, Pid=1,
Oid=2
union select Usid=5, Pid=2,
Oid=3
union select Usid=6, Pid=2,
Oid=4
union select Usid=7, Pid=1,
Oid=4
union select Usid=8, Pid=2,
Oid=2
union select Usid=7, Pid=2,
Oid=3
union select Usid=8, Pid=3,
Oid=4
)
----- Pull Total Response against every poll
,Total_Response as
(
SELECT O.PID,
Tot_Response=Count(*) from
User_Response O
Group By O.Pid
)
SELECT PID, Tot_Response,
[Option1]=Sum([Option1]),
[Option2]=Sum([Option2]),
[Option3]=Sum([Option3]),
[Option4]=Sum([Option4])
FROM
(
SELECT PID, Tot_Response,[Option1]=[1],
[Option2]=[2],[Option3]=[3],[Option4]=[4]
FROM (
----- Pull the data from above table
SELECT O.PID,O.Oid,
----- Pull all responses
Tot_Response=Isnull( TR.Tot_Response,0),
---- count option responses
Options_Response=Count(R.Pid) ,
---- Percentage = (Option Response/ Total Responses) x
100
Per_Result= Round(Cast(Count(R.Pid) as
float)/cast(Isnull( TR.Tot_Response,1)
as float)*100,2)
from Poll_Master P
JOIN Poll_Options O On P.Pid=O.Pid
LEFT JOIN User_Response R
On O.Pid=R.Pid
and O.Oid=R.Oid
LEFT JOIN Total_Response
TR on
O.Pid=TR.Pid
GROUP BY O.PID,O.Oid,TR.Tot_Response
) as s
----- Using Pivot to convert rows into columns
PIVOT
(
Sum(Per_Result) FOR
[Oid] IN
([1],[2],[3],[4])
)AS
pvt
)X
Group by PID, Tot_Response
order by PID
|
Conclusion
To generate the polls
results, we should also keep in mind that if any poll does not get any response
from user then it should be display on the grid also.
No comments:
Post a Comment