Use of Group by in (Oracle) SQL

Question

Relation_A(X,Y) where (X,Y) is the primary key for any tuple that belongs to Relation_A.

I want to write a query that gets the X(s) that occur once in Relation A and also get for which Y.

Attempt:

SELECT * 
  FROM (SELECT X, Y, COUNT(X) AS count 
          FROM Relation_A 
         GROUP BY X) WHERE count = 1;

This gives me an error saying that the nested query is not a GROUP BY expression.

Answer 1

WITH cte AS (
    SELECT X
    FROM Relation_A
    GROUP BY X
    HAVING COUNT(*) = 1
)
SELECT t1.*
FROM Relation_A t1
INNER JOIN cte t2
    ON t1.X = t2.x

Answer 2

SELECT a.*
FROM Relation_A a
INNER JOIN 
(SELECT X
FROM Relation_A
GROUP BY X
HAVING COUNT(*) = 1
) as b
ON a.X = b.x

Answer 3

Use a window function:

SELECT x,y 
FROM (
   SELECT x, y, count(*) over (partition by x) AS x_count
   FROM relation_A
)
WHERE x_count = 1;

Answer 4

You can use the 'having' clause:

SELECT X, Y, COUNT(X) AS count 
      FROM Relation_A 
     GROUP BY X HAVING count(X)=1;