SQL to find duplicate foreign keys in a group

Question

With tables, basically like this:

Elements
  id INT PRIMARY KEY
  ...

Observations
  id INT PRIMARY KEY
  ...

Data
  id INT PRIMARY KEY
  observation_id FOREIGN KEY
  element_id FOREIGN KEY
  value FLOAT
  ...

I want to find everything observation_idwhere there is a duplicate element_idin one observation_id. For example, if I have entries Data, for example:

1|50|23|4.5
2|50|24|9.9
3|66|23|4.4
4|66|23|4.1

Then the request will report observation_id 66, because it has two lines connected with element_id 23.

(I use PostgreSQL, but this is probably the main SQL question.)

Answer 1

Use the count () aggregate in conjunction with the have clause:

select observation_id, element_id, count (*)
from data
group by observation_id, element_id
having count (*)> 1