additional condition for sql query

Question

It is necessary to select groups whose students took part in all competitions. I made such a request. But the condition falls under it when there is only one such student in the group. You need two or more.

CREATE VIEW V3 AS
SELECT Groups
FROM R3
JOIN R1 USING (name)
GROUP BY Groups
HAVING COUNT (DISTINCT Competition) = (SELECT COUNT (DISTINCT Competition) FROM R1);

Tables

CREATE TABLE R1(
name VARCHAR(100),
article VARCHAR(100),
Competition VARCHAR(100));

CREATE TABLE R3 (
name VARCHAR(100),
Groups VARCHAR(100)
);

I tried adding the condition COUNT (DISTINCT Groups) > 1 but it not work

Answer 1

This is one way:

with cte as (select r1.name, Groups_c, count(r1.name) over(partition by Groups_c) cnt2
from r1
left join r3 on r1.name = r3.name
group by  r3.name,  Groups_c
having count(distinct Competition) = (select count(distinct Competition) from r1))
select r3.Groups_c
from r3
left join cte on r3.Groups_c = cte.Groups_c
group by r3.Groups_c
having count (distinct r3.name) = cte.cnt2 

DEMO