How to fetch the ids in a column where multiple columns are repeated

Question

I have a table like the next:

 TABLE 1
 -------
 ID        Integer
 COLUMN_1  Text
 COLUMN_2  Text
 COLUMN_3  Integer

How could I fetch the ids in which the combination of COLUMN_1 , COLUMN_2 and COLUMN_3 are repetead in the same column?

I count the columns repeated grouping it like next:

select  column_1, column_2, column_3, count(*) from table_ 1 group by icolumn_1, column_2, column_3;

But what I'm looking for is how to also get the Ids.

Example

So if I have the next data:

ID  | COLUMN_1 | COLUMN_2 | COLUMN_3
1   |     BLUE |  FISH    |  SEA
2   |     BLUE |  FISH    |  SEA
3   |     BLUE |  FISH    |  RIVER

I'm looking for a result like next:

 IDs Repeated |  COLUMN_1 | COLUMN_2 | COLUMN_3 | COUNT
     1, 2     |     BLUE  |  FISH    |    SEA   |   2
       3      |     BLUE  |  FISH    |    RIVER |   1

score 1 · Answer 1 · edited May 23 '17 at 11:45

1

It can be done with an aggregate function string_agg(expression, delimiter). There is a similar question here. It has an answer with examples.

edited May 23 '17 at 11:45

Community

1
1

answered Jan 13 '17 at 12:13

Abylay Sabirgaliyev

726
9
18

Yes and I know it, but how to combine both groups id group and columns 1, 2 and 3 group? – Pau Jan 13 '17 at 12:16

score 1 · Accepted Answer · answered Jan 13 '17 at 12:17

In your particular case, the following query should do what you want:

SELECT string_agg(id::varchar, ', ') AS "IDs Repeated",
  column_1, column_2, column_3, COUNT(*)
FROM p
GROUP BY column_1, column_2, column_3;

(I corrected what I believe was a typo in your result column name).

However, the result is not in first normal form. You might be interested by the following query:

SELECT id, column_1, column_2, column_3, COUNT(*) OVER (PARTITION BY column_1, column_2, column_3)
FROM p;

 id | column_1 | column_2 | column_3 | count 
----+----------+----------+----------+-------
  3 | BLUE     | FISH     | RIVER    |     1
  1 | BLUE     | FISH     | SEA      |     2
  2 | BLUE     | FISH     | SEA      |     2
(3 rows)

Thanks, that what I'm looking for – Pau Jan 13 '17 at 12:22 — Pau, Jan 13 '17 at 12:22

Mr. Bhosale · Answer 3 · 2017-01-13T12:28:38.173

1

Check This.

        SELECT array_to_string(array_agg(id), ',') as "IDs Repetead",
        COLUMN_1,COLUMN_2, COLUMN_3,count(id) as "COUNT" 
        FROM temp
        GROUP BY COLUMN_1,COLUMN_3,COLUMN_2
        order by  "IDs Repetead"

edited Jan 13 '17 at 12:28

answered Jan 13 '17 at 12:22

Mr. Bhosale

3,018
1
17
34

`array_to_string(array_agg(id), ',')` can be simplified to `string_agg(id::text, ','))` – Jan 13 '17 at 12:44
Yes .. We can also use this. – Mr. Bhosale Jan 13 '17 at 12:46

How to fetch the ids in a column where multiple columns are repeated

Example

3 Answers3