How to create comma-separated values in 1 column

Question

This is my sample table, is it possible to get the distinct value of animals and get its letter with commas and count the letter of specific animals?

Query: Select animals, letter From pet;

 +--------------+--------------+
 | animals      | letter       |
 +--------------+--------------+
 | dog          |    d         |
 | dog          |    o         |
 | dog          |    g         |
 | cat          |    c         |
 | cat          |    a         |
 | cat          |    t         |
 | mouse        |    m         |
 | mouse        |    s         |
 | mouse        |    e         |
 +--------------+--------------+

This is my expected output, count the letter to get total, count every item before the comma symbol,:

 +--------------+--------------+--------------+
 | animals      | letter       |   total      |
 +--------------+--------------+--------------+
 | dog          |    d,o,g     |     3        |
 | cat          |    c,a,t     |     3        |
 | mouse        |    m,s,e     |     3        |
 +--------------+--------------+--------------+

How do you expect the letters to be returned in the order you are showing? There is nothing in your tables that put the letters in any order. — Dour High Arch, Nov 05 '14 at 02:16
So applying the linked question to your table: `SELECT animals, GROUP_CONCAT(letter ORDER BY letter ASC) FROM thetable GROUP BY animals` — Michael Berkowski, Nov 05 '14 at 02:17
the link answer is correct to my question, but my concern is what if I get the sum of total letter, for example dog = 3, cat = 3, mouse = 3. And my columns will be animals, letter, total — Top Out, Nov 05 '14 at 03:10
What does “sum of total letter” mean? The count of letters? Investigate `Count(letter)` and `GROUP BY`. If you can't get it to work, post your efforts to a new question. — Dour High Arch, Nov 05 '14 at 03:37
sum(char_length(letter)) as total , but I want is to get seperated by comma(,) as count sample ab,cd,ef,gh,ih = 5 — Top Out, Nov 05 '14 at 06:19
Im done thanks for the help ... sum(CHAR_LENGTH(letter) - CHAR_LENGTH(REPLACE(letter, ',', '')) + 1) as total — Top Out, Nov 05 '14 at 06:28

How to create comma-separated values in 1 column

0 Answers0