Simple SQL Query to count poll results

Question

What is the best and efficient way to count poll results from PollResponses table for a particular PollId as per db schema shown below

I want to have following type of Data if we have four option for that particular poll

optionText
count
optiontext
count
optiontext
count
optiontext
count

Example

Obama
2000
Romney
1800
Clinton
3000
Xyz
1200

total 8 rows fetched

I am using SQL Server 2012 sp1 update

using below query

select options.OptionText as [optionText]
,count(responses._id) as [count]
from Polls polls
inner join PollOptions options on options.PollId = polls._id
inner join PollResponses responses on responses.PollId = polls._id
where polls._id = 104
group by options.OptionText, polls._id

with this data of pollresponse table

i m getting this result

while i should get No 0 Yes 1

current data of all three tables

1. Polls

2. PollOptions

3. PollResponse

Answer 1

I believe this will return the result you want:

select po.optiontext,
  count(pr.optionid) Total
from PollOptions po
left join polls p
  on p._id = po.pollid
left join pollresponse pr
  on p._id = pr.pollid
  and pr.optionid = po._id
-- where p._id = 104
group by po.optiontext;

See SQL Fiddle with Demo

Based on the sample data you provided the result will be:

| OPTIONTEXT | TOTAL |
----------------------
|     HOD IT |     1 |
|   HOD Mech |     1 |
|     Mr. CS |     2 |
|         no |     0 |
|        yes |     1 |

Edit, if you want to order the data by option id, then you will have to include it in the group by:

select po.optiontext,
  count(pr.optionid) Total
from PollOptions po
left join polls p
  on p._id = po.pollid
left join pollresponse pr
  on p._id = pr.pollid
  and pr.optionid = po._id
--where p._id = 104
group by po.optiontext, po._id
order by po._id;

See SQL Fiddle with Demo

Results:

| OPTIONTEXT | TOTAL |
----------------------
|     Mr. CS |     2 |
|     HOD IT |     1 |
|   HOD Mech |     1 |
|        yes |     1 |
|         no |     0 |

Answer 2

I believe the query you have written is missing a join . . . which is also missing from the ER diagram you show. However, the results table has an OptionID. Presumably, there are multiple options for a given poll. So try this:

select options.OptionText as [optionText], polls._id, count(responses._id) as [count]
from Polls polls inner join
     PollOptions options
     on options.PollId = polls._id inner join
     PollResponses responses
     on responses.PollId = polls._id and responses.OptionId = options._Id
where polls._id = 104
group by options.OptionText, polls._id

If responses and options are not connected, then you cannot directly get the number of responses for each option.

Answer 3

While I don't have your data to work with, here's a VERY rough (i.e. untested) idea of what you should be doing. You essentially INNER JOIN your tables and then use the aggregate function COUNT to count all the responses for each grouped candidate.

select options.OptionText as [optionText]
,count(responses._id) as [count]
from Polls polls
inner join PollOptions options on options.PollId = polls._id
inner join PollResponses responses on responses.PollId = polls._id
where polls._id = @pollid
group by options.OptionText, polls._id

Relating to efficiency, I can see you're using primary keys and foreign key constraints; another performance improvement you could make would be to index your OptionText column on your PollOptions table.

Answer 4

SELECT 
    po.OptionText AS [optionText],
    COUNT(pr._id) AS [count]
FROM PollResponses pr
JOIN Polls p ON (p._id = pr.PollID)
JOIN PollOptions po ON (po.PollID = p._id)
WHERE p._id = @pPollId
GROUP BY po.OptionText, pr._id