Oracle SQL: Dividing Counts into unique and non unique columns

I have a table that looks like this:

|FileID|  File Info   |
| ---- | ------------ |
|   1  | X            |
|   1  | Y            |
|   2  | Y            |
|   2  | Z            |
|   2  | A            |

I want to aggregate by FileID and split the File Info column into 2 separate count columns. I want 1 column to have the count of the Unique File Info and the other to be a count of non-Unique file info.

The result would ideally look like this:

|FileID| Count(Unique)| Count(Non-unique) |
| ---- | ------------ | ----------------- |
|   1  | 1            | 1                 |
|   2  | 2            | 1                 |

where the non-unique count is the ‘Y’ and the unique count is from the ‘X’ and ‘Z, A’ for FileID 1 and 2 respectively.

I’m looking for ways to gauge uniqueness between files rather than within.

Answer

Use COUNT() window function in every row to check if FileInfo is unique and then use conditional aggregation to get the results that you want:

SELECT FileID,
       COUNT(CASE WHEN counter = 1 THEN 1 END) count_unique,
       COUNT(CASE WHEN counter > 1 THEN 1 END) count_non_unique
FROM (
  SELECT t.*, COUNT(*) OVER (PARTITION BY t.FileInfo) counter
  FROM tablename t
) t
GROUP BY FileID; 

See the demo.