Counting Poll Data in 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?

How would you format this data? 3 columns for each question (9 columns x 1 row), or 1 row for each question (3 rows x 3 columns), or something else? — cyberbit, Jun 03 '16 at 16:55
@Strawberry I'm assuming that your suggestion is that my database is set up all wrong? It likely is. Would your suggestion be to separate each question out into it's own table? — Gareth Dackevych-Thomas, Jun 05 '16 at 08:09
No. My suggestion is to normalise your data. In its simplest form, that might mean having two columns; one which holds the question number, and one which holds (presumably) the question itself. — Strawberry, Jun 05 '16 at 09:19

Gareth Dackevych-Thomas · Answer 1 · 2016-06-05T08:04:51.990

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.

Counting Poll Data in MySQL

1 Answers1