Count all the time the same value appears with SQL-CodePudding

Welcome to another day, another SQL question.

I'm trying to list all the time, a value appears for all users.

I did something like that :

$nRows = $pdo->query('SELECT choix1, count(*) as newchoice from users GROUP BY choix1')->fetchAll();

List of choice from users

List of city and available jobs

database

What I need is to be able to get the number of time a city was selected, not only from choice 1, then choice 2 ... but from all choice at the same time.

That number will be divided by the number of jobs available to get a "ratio" that tells how much a city is desired (I know, that the city and jobs are on the same col, but I failed exporting my csv, but it's another problem, I will start by entering the number of job by hand).

$nRows = $pdo->query('SELECT choix1, count(*) as newchoice from users GROUP BY choix1')->fetchAll(); 
$nRows2 = $pdo->query('SELECT choix2, count(*) as newchoice from users GROUP BY choix2')->fetchAll(); 
$nRows3 = $pdo->query('SELECT choix3, count(*) as newchoice from users GROUP BY choix3')->fetchAll(); 
$nRows4 = $pdo->query('SELECT choix4, count(*) as newchoice from users GROUP BY choix4')->fetchAll(); 
$nRows5 = $pdo->query('SELECT choix5, count(*) as newchoice from users GROUP BY choix5')->fetchAll(); 
$nRows6 = $pdo->query('SELECT choix6, count(*) as newchoice from users GROUP BY choix6')->fetchAll(); 

foreach ($nRows as $nRow) {
    print_r($nRows);
    echo ("<br>");
    $vchoix1=$nRow[1] / 4;
    echo ("<br>");
    echo($vchoix1);
}

This is my actual crappy code.

Thanks for reading. Have a nice day.

CodePudding user response：

Use a Union to turn the many horizontal columns into a single column:

select choix, count(*)
from (
    select choix1 choix from users
    union all
    select choix2 from users
    union all
    select choix3 from users
    union all
    select choix4 from users
    union all
    select choix5 from users
    union all
    select choix6 from users
) u
group by choix