1

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).

Reut
  • 1,555
  • 4
  • 23
  • 55
  • What is your database? – Shadiqur Nov 16 '21 at 09:33
  • @Shadiqur I don't understand the question. there is example for the table – Reut Nov 16 '21 at 09:45
  • 2
    Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Nov 16 '21 at 10:25

2 Answers2

1

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 |
+--------+-------------+--------------+
Eric Lin
  • 1,440
  • 6
  • 9
0

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
Shadiqur
  • 490
  • 1
  • 5
  • 18