Wednesday, February 21, 2018

TSQL- SQL Query to count poll results

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=
union select Usid=2, Pid=1, Oid=
union select Usid=3, Pid=1, Oid=3
union select Usid=6, Pid=1, Oid=4
union select Usid=1, Pid=2, Oid=
union select Usid=2, Pid=2, Oid=
union select Usid=3, Pid=2, Oid=3
union select Usid=7, Pid=2, Oid=4
union select Usid=5, Pid=1, Oid=
union select Usid=6, Pid=1, Oid=
union select Usid=5, Pid=2, Oid=3
union select Usid=6, Pid=2, Oid=4
union select Usid=7, Pid=1, Oid=
union select Usid=8, Pid=2, Oid=
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
PID
Tot_Response
Option1
Option2
Option3
Option4
1
7
14.29
28.57
14.29
42.86
2
8
12.50
25.00
37.50
25.00
3
1
0.00
0.00
0.00
100.00
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

Popular Posts