0

i have a table below that records set responses from users for several different polls; results are stored in the responce column

CREATE TABLE IF NOT EXISTS `results` (
  `poll_id` int(11) NOT NULL,
  `response` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `ip_number` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  KEY `poll_result` (`poll_id`,`poll_answer`)
)

Each poll has a unique Id; for example a poll asking users to choose between two cars would have poll_id of 1 BUT within the response column would have the responses for two possible cars e.g range or ford. 

SELECT poll_answer  FROM `results` WHERE poll_id = 1  AND   poll_answer = 'Range'

i now need to draft an SQl query to determine the following;

  1. total number and percentage of users, from poll 1, who chose Range Rover as their favorite car

  2. total and percentage of users, from poll 1 , who chose ford.

  3. total number of users who responded to poll 1**

i know how to get the total from a column but not how to get two total from the same column (with two different where clauses); and to then calculate the percentage.

SELECT Count(responce) FROM `results` WHERE poll_id = 1 AND response = 'range'
Paul Kendal
  • 559
  • 9
  • 24

2 Answers2

0

You could just use a simple grouping to give you everything or filter out by adding your where clause if that's what you want.

Select poll_id, (Select Count(poll_id) from results where poll_id = r.poll_id group by poll_id) Total_in_Poll, response, count(response) Total_responded, (Count(response) / ((Select Count(poll_id) from results where poll_id = r.poll_id group by poll_id) * 1.0)  * 100.00) Percent_responded
FROM results r
GROUP BY poll_id, response
order by poll_id
Christian Barron
  • 2,695
  • 1
  • 14
  • 22
  • hi Christian. thanks for answering. i tried your answer in mysql but got the followng error report: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'float) * 100.00) Percent_responded FROM results GROUP BY poll_id, response LIMI' at line 1 – Paul Kendal Feb 05 '15 at 11:11
  • @PaulKendal Can you try the updated? I keep forgetting MySQL doesn't like casting floats – Christian Barron Feb 05 '15 at 11:14
  • thanks so much for help so far. i tried what u suggested but it gives me this answer for the percentage: 100.000000. this is obviously wrong. i have 12 entries in the response column, 10 ranges and 2 fords. – Paul Kendal Feb 05 '15 at 11:29
  • @PaulKendal sorry that's me being a dingbat, try again. – Christian Barron Feb 05 '15 at 11:46
  • hey Christian. brilliant. it works in mysqlAdmin. quick question though. when i try to extract the values on my php script i get this message: mysql_fetch_assoc() expects parameter 1 to be resource, object given this how i try to extract it: $row = mysql_fetch_assoc($result). – Paul Kendal Feb 05 '15 at 15:56
  • @PaulKendal Not sure, have a look at this and see if it helps: http://stackoverflow.com/questions/3129374/how-to-prevent-this-error-warning-mysql-fetch-assoc-expects-parameter-1-to – Christian Barron Feb 05 '15 at 16:10
  • hi Christian, thanks a million for your help. it works now. brilliant advice – Paul Kendal Feb 06 '15 at 10:18
0

You can do this by summing a conditional expression:

SELECT count(case poll_answer when 'Range' then 1 end) total_range,
       count(case poll_answer when 'Range' then 1 end) 
             * 100 / count(*)                          perc_range,
       count(case poll_answer when 'Ford' then 1 end)  total_ford,
       count(case poll_answer when 'Ford' then 1 end) 
             * 100 / count(*)                          perc_ford,
       count(*)                                        total_response              
FROM `results` 
WHERE poll_id = 1