计算多列的更好方法

Question

I am currently using the following to count multiple columns, it basically adds the total for answer1, answer2 .....etc etc columns up to answer30.

So the results look like

1x
4x
3x
3x
4x 

etc

Is there a better way I can put all of this code into one query ?.

    $sql1 = <<<SQL
        SELECT answer1, COUNT(answer1)
        FROM `QuestionnaireAnswers`
        WHERE questionnaireID='$questionnaireID'
        GROUP BY answer1
    SQL;
    if(!$result1 = $db->query($sql1)){ die('There was an error running the query [' . $db->error . ']');}

    while($row1 = $result1->fetch_assoc()){ 
    echo $row1['COUNT(answer1)'] . ' X <strong>' . $answer1 . '</strong><br />';
    }

    $sql2 = <<<SQL
        SELECT answer2, COUNT(answer2)
        FROM `QuestionnaireAnswers`
        WHERE questionnaireID='$questionnaireID'
        GROUP BY answer2
    SQL;
    if(!$result2 = $db->query($sql2)){ die('There was an error running the query [' . $db->error . ']');}

    while($row2 = $result2->fetch_assoc()){ 
    echo $row2['COUNT(answer2)'] . ' X <strong>' . $answer2 . '</strong><br />';
    }

    $sql3 = <<<SQL
        SELECT answer3, COUNT(answer3)
        FROM `QuestionnaireAnswers`
        WHERE questionnaireID='$questionnaireID'
        GROUP BY answer3
    SQL;
    if(!$result3 = $db->query($sql3)){ die('There was an error running the query [' . $db->error . ']');}

    while($row3 = $result3->fetch_assoc()){ 
    echo $row3['COUNT(answer3)'] . ' X <strong>' . $answer3 . '</strong><br />';
    }

    $sql4 = <<<SQL
        SELECT answer4, COUNT(answer4)
        FROM `QuestionnaireAnswers`
        WHERE questionnaireID='$questionnaireID'
        GROUP BY answer4
    SQL;
    if(!$result4 = $db->query($sql4)){ die('There was an error running the query [' . $db->error . ']');}

    while($row4 = $result4->fetch_assoc()){ 
    echo $row4['COUNT(answer4)'] . ' X <strong>' . $answer4 . '</strong><br />';
    }

    $sql5 = <<<SQL
        SELECT answer5, COUNT(answer5)
        FROM `QuestionnaireAnswers`
        WHERE questionnaireID='$questionnaireID'
        GROUP BY answer5
    SQL;
    if(!$result5 = $db->query($sql5)){ die('There was an error running the query [' . $db->error . ']');}

    while($row5 = $result5->fetch_assoc()){ 
    echo $row5['COUNT(answer5)'] . ' X <strong>' . $answer5 . '</strong><br />';
    }

    $sql6 = <<<SQL
        SELECT answer6, COUNT(answer6)
        FROM `QuestionnaireAnswers`
        WHERE questionnaireID='$questionnaireID'
        GROUP BY answer6
    SQL;
    if(!$result6 = $db->query($sql6)){ die('There was an error running the query [' . $db->error . ']');}

    while($row6 = $result6->fetch_assoc()){ 
    echo $row6['COUNT(answer6)'] . ' X <strong>' . $answer6 . '</strong><br />';
    }

    $sql7 = <<<SQL
        SELECT answer7, COUNT(answer7)
        FROM `QuestionnaireAnswers`
        WHERE questionnaireID='$questionnaireID'
        GROUP BY answer7
    SQL;
    if(!$result7 = $db->query($sql7)){ die('There was an error running the query [' . $db->error . ']');}

    while($row7 = $result7->fetch_assoc()){ 
    echo $row7['COUNT(answer7)'] . ' X <strong>' . $answer7 . '</strong><br />';
    }

    $sql8 = <<<SQL
        SELECT answer8, COUNT(answer8)
        FROM `QuestionnaireAnswers`
        WHERE questionnaireID='$questionnaireID'
        GROUP BY answer8
    SQL;
    if(!$result8 = $db->query($sql8)){ die('There was an error running the query [' . $db->error . ']');}

    while($row8 = $result8->fetch_assoc()){ 
    echo $row8['COUNT(answer8)'] . ' X <strong>' . $answer8 . '</strong><br />';
    }

    $sql9 = <<<SQL
        SELECT answer9, COUNT(answer9)
        FROM `QuestionnaireAnswers`
        WHERE questionnaireID='$questionnaireID'
        GROUP BY answer9
    SQL;
    if(!$result9 = $db->query($sql9)){ die('There was an error running the query [' . $db->error . ']');}

    while($row9 = $result9->fetch_assoc()){ 
    echo $row9['COUNT(answer9)'] . ' X <strong>' . $answer9 . '</strong><br />';
    }

Answer 1

One approach:

select answerNum, answerVal, count(answerVal) from
(select n.num answerNum,
        case n.num
            when 1 then a.answer1
            when 2 then a.answer2
            ...
        end answerVal
 from (select 1 num union select 2 union ...) n
 cross join `QuestionnaireAnswers` a
 WHERE questionnaireID='$questionnaireID') sq
group by answerNum, answerVal