How to get grouping of rows in SQL

Question

I have a table like this:
id  name
1   washing
1   cooking
1   cleaning
2   washing
2   cooking
3   cleaning

and I would like to have a following grouping

id  name                      count
1   washing,cooking,cleaning    3
2   washing,cooking             2
3   cleaning                    1

I have tried to group by ID but can only show count after grouping by

SELECT id,
       COUNT(name)
FROM WORK
GROUP BY id

But this will only give the count and not the actual combination of names.

I am new to SQL. I know it has to be relational but there must be some way.

Thanks in advance!

ScaisEdge · Answer 1 · 2019-06-26T19:32:50.897

0

in postgresql you can use array_agg

SELECT id, array_agg(name),  COUNT(*)
FROM WORK
GROUP BY id

in mysql you can use group_concat

SELECT id, group_concate(name),  COUNT(*)
FROM WORK
GROUP BY id

or for redshift

SELECT id, listagg(name),  COUNT(*)
FROM WORK
GROUP BY id

edited Jun 26 '19 at 19:32

answered Jun 26 '19 at 18:19

ScaisEdge

Thanks for your reply. However I get this ```function array_agge(character varying) does not exist ``` – Yogesh Singh Jun 26 '19 at 18:24
answer uodated .. is array_agg – ScaisEdge Jun 26 '19 at 18:25
I still get same error ``` [Amazon](500310) Invalid operation: function array_agg(character varying) does not exist; ``` – Yogesh Singh Jun 26 '19 at 18:27
1

which db you are really using .. mysql? .. postgresql .?? .. or some other db .. ??? – ScaisEdge Jun 26 '19 at 18:28
Thanks. The listagg seems to work, but its not grouping. I am getting like: ``` cleaning 1 cleaning 1 cleaning 1 ``` what i want is cleaning 3 – Yogesh Singh Jun 26 '19 at 19:31
are you sure you have not empty string ..check for the real content of your data – ScaisEdge Jun 26 '19 at 19:33
Yes, there is no empty string. Grouping doesnt seem to work – Yogesh Singh Jun 26 '19 at 19:38

1 Answers1