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?

possible duplicate of [How to use GROUP\_CONCAT in a CONCAT in mysql](http://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql) — WorkSmarter, Jul 15 '15 at 04:54

score 0 · Answer 1 · answered Jul 15 '15 at 04:56

Group_concat the values and join back on itself counting the fields:

SELECT key,languages,translated_values/COUNT(key) translated
FROM table b 
INNER JOIN 
(SELECT key, GROUP_CONCAT( languages SEPARATOR ',') languages, 
COUNT(*) as translated_values 
FROM table 
WHERE translated!=""
GROUP BY key) a ON a.key=b.key
GROUP BY key,translated_values,b.languages;

score 0 · Accepted Answer · answered Jul 15 '15 at 05:14

0

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

answered Jul 15 '15 at 05:14

M Khalid Junaid

63,861
10
90
118

1

thanks for answer, it's what i'm looking for – Xerton Web Jul 15 '15 at 05:58

keep distinct key and concat column

2 Answers2