在MySQL中计算民意调查数据

Question

I have data in a mySQL table with columns:

question 1 | question2 | question3 | question4

and data that will be a number from 1 to 3 for each of them. I want to count the number of 1's, 2's and 3's for each question and have the following, which doesn't work.

SELECT
     question1 as q1
    ,COUNT(question1) as q1C
     ,question2 as q2
    ,COUNT(question2) as q2C
     ,question3 as q3
    ,COUNT(question3) as q3C
     ,question4 as q4
    ,COUNT(question4) as q4C
    FROM pollOne
     WHERE question1 != 0
     AND  question2 != 0
     AND  question3 != 0
     AND  question4 != 0
     GROUP BY q1, q2, q3, q4

If I split that to a single question, then it does work as below:

SELECT
    question1 as q1
    ,COUNT(question1) as q1C
    FROM pollOne
    WHERE question1 != 0
    GROUP BY q1

But I'd rather be able to do it in one go, if that's possible. Any ideas anyone?

In part answer to my own question, but not a solution, I have come across this link in the stack:

https://stackoverflow.com/a/2421441/5880604

Great explanation on the Group By usage in mySQL and makes it clear why what I have been trying to do won't work.

I would still like to know if it is possible though, if any one has any insight?

Answer 1

Ok, a crude way of overcoming my problem was to iterate over each question individually, pushing each to an array as I went.

$data = array();

for ($i=1; $i < 5; $i++) { 
    $sql = "SELECT
        question" . $i . " as q" . $i . "
        ,COUNT(question" . $i . ") as q" . $i . "C
        FROM pollOne
        WHERE question" . $i . " != 0
        GROUP BY q" . $i;
        $result = mysqli_query($connection, $sql);
        while ($row = mysqli_fetch_assoc($result)){
            array_push($data, $row);
        }
}

I does work and since this is only a small project lasting only for a few days, then I'm happy with this.