how to group data into 1 string in mysql after group by-CodePudding

i have one table error of employ like this

CREATE TABLE fruit(
    id int(12) AUTO_INCREMENT,
    fruit_type varchar(10)
    color n_varchar(50)
    PRIMARY KEY (id)
);

I grouped by fruit_type but noted it was overwritten by the latest one, like this

when I query select it will be like this

|id| fruit_type|  color   |
|--|-----------|----------|
|1 |apple      |green     |
|2 |apple      |not green |
|3 |banana     |green     |

when i group by

|id| fruit_type|  color   |count
|--|-----------|----------|--------
|1 |apple      |green     |2
|3 |banana     |green     |1

what i want

|id| fruit_type|     color     |count_fruit_type
|--|-----------|---------------|--------
|1 |apple      |green,not green|2
|3 |banana     |green          |1

CodePudding user response：

first you can group similar fruit_types together and than concatenate them using group_concat

SELECT fruit_type,GROUP_CONCAT(color), COUNT(id) FROM fruit
GROUP BY fruit_type;

CodePudding user response：

    CREATE TABLE t (
        v CHAR
    );
    
    INSERT INTO t(v) VALUES('A'),('B'),('C'),('B');

SELECT 
    GROUP_CONCAT(v
        ORDER BY v ASC
        SEPARATOR ';')
FROM
    t;

You will get A;B;B;C