keep distinct key and concat column

Question

I have a table for translation for example:

|     key        |    language    |    value
----------------------------------------------
|       hi       |      en        |    hello
|       hi       |      es        |    hola
|       delete   |      en        |    value
|       delete   |      es        |    suprimir
|       city     |      en        |    
|       city     |      es        |    ciudad

For the moment there are 2 languages but I will add more later. I'm trying to find a sql query which group "key" AND concat "language" with not empty value.

I'm looking for this output:

|    key    |   languages   |     translated
---------------------------------------------------
|    hi     |     en,es     |       100%
|   delete  |     en,es     |       100%
|    city   |     es        |       50%

Any idea?

Answer 1

You can do so in single query

select `key`,
group_concat(
  case when `value` is not null 
  then `language` 
  else null end) languages,
concat(
  (sum(`value` is not null )/ count(distinct `language`) ) * 100,
  '%') translated
from table1
group by `key`

DEMO