Create new column with all unique values from another column in SQL-CodePudding

I have the following table:

>>> id   crop     grower    loc
0  11    maize    Lulu    Fiksi
1  13    maize    Lulu    Menter
2  05    maize    Felix   Hausbauch
3  04    apples   Lulu    Fiksi
4  02    apples   Meni    Linter
5  06    cotton   Delina  Marchi
6  12    cotton   Lexi    Tinta
7  16    cotton   Lexi    Ferta
...

I want tto create new table which will show the unique crop names, count of the crops appearence and then list of all the growers that grow this crop,so the result table should look like this:

>>>     crop   total_count   growers
0       maize    3           Lulu, Felix
1       apples   2           Lulu,Meni
2       cotton   3           Delina, Lexi

I manage to create table that shows the crops and the total count without the growers names:

select "CROP",count(*) "totalCount"
from "table"
group by "CROP"
order by "totalCount" desc

My question is how can I create new table with new column that contains list of unique growers for each crop (like in the example).

CodePudding user response：

GROUP_CONCAT is for MySQL, Snowflake uses LISTAGG:

create or replace table test (
    id int,
    crop varchar,
    grower varchar,
    loc varchar
);

insert into test values 
(11, 'maize', 'Lulu', 'Fiksi'),
(13, 'maize', 'Lulu', 'Menter'),
(5, 'maize', 'Felix', 'Hausbauch'),
(4, 'apples', 'Lulu', 'Fiksi'),
(2, 'apples', 'Meni', 'Linter'),
(6, 'cotton', 'Delina', 'Marchi'),
(12, 'cotton', 'Lexi', 'Tinta'),
(16, 'cotton', 'Lexi', 'Ferta');

select
    crop,
    count(1) as total_count,
    listagg(distinct grower, ', ') as growers
from test
group by crop
;

 -------- ------------- -------------- 
| CROP   | TOTAL_COUNT | GROWERS      |
|-------- ------------- --------------|
| maize  |           3 | Lulu, Felix  |
| apples |           2 | Lulu, Meni   |
| cotton |           3 | Delina, Lexi |
 -------- ------------- --------------

CodePudding user response：

you can use GROUP_CONCAT() or any related fun according to your data base

select "CROP",count(*) "totalCount",GROUP_CONCAT(grower) as growers
from "table"
group by "CROP"
order by "totalCount" desc