CODEWITHSUNDEEP
sqlpostgresqlcountpostgresql-9.4
Pau
Pau

Reputation: 16116

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

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

Upvotes: 0

Views: 124

Answers (3)

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

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"

Upvotes: 1

Fabian Pijcke
Fabian Pijcke

Reputation: 3210

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)

Upvotes: 1

Abylay Sabirgaliyev
Abylay Sabirgaliyev

Reputation: 726

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

Upvotes: 1

Related Questions