sql delete based on group by

Question

I have following query:

select user_id, trajectory_id, count(trajectory_id) from point group by user_id, trajectory_id

It returns following result:

And I want to delete all records from point table where count in produced table is < 5.

Right now I have following query, but it is not compiling. What is wrong with it and how can I fix it?

delete 
from point
where count(trajectory_id) < 5
GROUP BY user_id, trajectory_id

I am using Postgres.

Answer 1

delete p1
from point p1
join
(
  select user_id, trajectory_id 
  from point 
  group by user_id, trajectory_id
  having count(trajectory_id) < 5
) p2 on p1.user_id = p2.user_id
    and p1.trajectory_id = p2.trajectory_id 

Answer 2

I would do this with a simple correlated subquery:

delete from point
    where (user_id, trajectory_id) in (select user_id, trajectory_id
                                       from point
                                       group by user_id, trajectory_id
                                       having count(*) < 5
                                      );

Answer 3

There are some syntax errors to the above accepted answer, the below should do the trick,

delete 
from point p1
using
(
  select user_id, trajectory_id 
  from point 
  group by user_id, trajectory_id
  having count(trajectory_id) < 5
) p2 
where p1.user_id = p2.user_id
    and p1.trajectory_id = p2.trajectory_id