Search code examples
phpmysqlfind-occurrences

Count occurrences of values and return them as value_name=>occurences pair

I have a translation table with a column that contains values repeatedly (language name). As following:

id | neutral text | language | translation
---+--------------+----------+------------
 0 |       SUBMIT |       en |      Submit
 1 |       SUBMIT |       cs |     Odeslat
 2 |       SUBMIT |       fr |    Démarrer
 3 |          DAY |       fr |        Jour
 4 |       MONDAY |       fr |       Lundi
 5 |       MONDAY |       cs |     Pondělí

Now, as you can see, I have intentionally omitted to fill all the translations for DAY and MONDAY. What I need to find out by a query is, which records are incomplete. This means, find cells that have less occurrences for "neutral text" than others.
Is there possible anything besides FOR loop in PHP? Downloading whole table and looping it makes MySql useless at this moment.

Solution

  • You can do this with aggregation and a having clause:

    select neutraltext
from t
group by neutraltext
having count(*) < (select count(*) from t group by neutraltext order by count(*) desc limit 1);