Could anyone help me with the script for this

Question

have a table like this

    empid    questionid   options
    1        1            A
    2        1            A
    3        1            B
    4        1            C

now i need result like this

    questionid   responseA   responseB   responseC
    1            50%         25%         25%

Answer 1

SELECT CAST(100/
  ( SELECT COUNT(*)
    FROM your_Table as t2
    WHERE t2.questionid = t1.questionid )
  * COUNT(*) AS VARCHAR) + '%' AS 'Percent', OPTIONS, questionid
FROM your_Table as t1
--WHERE questionid = 2
GROUP BY OPTIONS, questionid
ORDER BY questionid;

this is one possible way u could do it (works on SQL-Server but not sure if it does in MySql)

greets Auro

Answer 2

You could PIVOT;

SELECT questionid, (A / total) * 100 responseA, (B / total) * 100 responseB, (C / total) * 100 responseC FROM (
    SELECT T1.questionid, T1.options, T2.total
    FROM the_tbl T1
    INNER JOIN (SELECT questionid, CAST(COUNT(*) AS MONEY) AS total FROM the_tbl GROUP BY questionID) T2 ON T2.questionid = T1.questionid
) T
PIVOT (
    COUNT(options) FOR options IN ([A], [B], [C])
) AS pvt
ORDER BY questionid

Answer 3

T-SQL:

SELECT 
  questionid,
  SUM(CASE options WHEN 'A' THEN 100.0 ELSE 0.0 END) / COUNT(options)   AS responseA,
  SUM(CASE options WHEN 'B' THEN 100.0 ELSE 0.0 END) / COUNT(options)   AS responseB,
  SUM(CASE options WHEN 'C' THEN 100.0 ELSE 0.0 END) / COUNT(options)   AS responseC
FROM 
  answers
GROUP BY
  questionid

Note: To avoid casting and multiplying 100, I used 100.0 and 0.0 values in CASE ... END expressions.