0

I have a table where I want to gather some statistics, but only for items that are repeated two or more times. To simply, here is how the table looks like:

CREATE TABLE test (
    id bigint,
    something text
);

Most rows have a unique id, some have repeated entries where something differs.

What I'm wondering is whether there is a way to read the table but only items that have 2 or more rows with the same id.

I know how to count the number of duplicated roes using the COUNT() and GROUP BY:

SELECT id, COUNT(id) AS count FROM test GROUP BY id;

I don't really see how to use the COUNT(id) in a WHERE or FILTER clause (since aggregate functions are not allowed there).


Just in case, a very small data sample would be:

 id | something
----|-----------
 1  | first
 2  | second
 2  | third

In the result I want to see:

 id | something
----|-----------
 2  | second
 2  | third
2
  • "some have repeated entries where something differs" The something is required to differ because of a constraint, or you want to count cases only where they do differ, or you just happen to know that they differ but don't want to do anything about it?
    – jjanes
    Feb 2, 2023 at 21:15
  • @jjanes They are likely to differ. The constraint is over 5 columns, which is not represented here. But for what I'm trying to do here, we can consider that they always differs. Feb 2, 2023 at 21:40

1 Answer 1

1

I think you will need some kind of subquery to do this, there is no "single-level" way to do it.

One way is with a window function. This needs a subquery because there is no way to reference the wcount in the same level at which it was computed:

select id,something from (
    select *, count(*) over (partition by id) from test as wcount
) foo where wcount>1;

Another way is with a join to an aggregated subquery:

select * from test 
join 
(select id from test group by id having count(*) >1) foo
using (id);

An advantage of the second form is that you change count(*) to count(distinct something) if that is what you want to do, but that would produce an error if done in the window-function technique.

Another method would be with an EXISTS subquery, but that is slower in my hands:

select * from test 
where exists (
    select 1 from test t2 where t2.id=test.id and t2.something <> test.something
)

If you didn't want to force "something" to differ in order to count them, then you could resort to using the system column ctid in the <>.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.