Select all rows where a column value occurs more than once

Question

I have a following table structure in Postgres:

 id | account_id | plan_id | active
----+------------+---------+--------
  1 |    0cYd7Ak |       1 |      f
  2 |    Uk02q1d |       1 |      t
  3 |    eRlk810 |       2 |      f
  4 |    Uk02q1d |       2 |      t
  5 |    0cYd7Ak |       1 |      t
  6 |    yT3nv3p |       3 |      t  

How I can select all row where account_id occurs more than once? For the example above it will return rows 1, 2, 4, 5

I tried this one query, but it does not return what I expect:

select * from table_name t1 
where (select count(*) from table_name t2 
  where t1.account_id = t2.account_id) > 1 
order by t1.account_id;

Answer 1

Your query seems correct (although it may not be the most efficient).

There are many ways to write this type of query. For example, you could use EXISTS to avoid counting in the correlated subquery:

select * from table_name t1 
where exists 
      (select 1 from table_name t2 
       where t1.account_id = t2.account_id and t1.id <> t2.id) 
 ;

Another method is to use a subquery or CTE and window aggregate:

select id, account_id, plan_id, active
from 
  ( select *, 
           count(1) over (partition by account_id) as occurs
    from table_name
  ) AS t 
where occurs > 1 ;

Or use a subquery to find the accounts that have more than one occurrences and then join to the table:

select t.*
from 
  ( select account_id
    from table_name
    group by account_id
    -- having count(1) > 1          
    having min(id) <> max(id)      -- variation with same result 
  ) as c
  join table_name as t 
  on t.account_id = c.account_id ;

Answer 2

You can also try with HAVING clause.

select account_id, count(id) as repetita
from t1
group by account_id

having count(id) > 1 ;

with this solution remember to add the field you want to output to group by also