By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH yourTable AS (
SELECT 1 AS id, 'A1' AS A, 'B1' AS B, 'C1' AS C FROM dual UNION ALL
SELECT 2, 'A1', 'B2', 'C1' FROM dual UNION ALL
SELECT 3, 'A1', 'B1', 'C2' FROM dual UNION ALL
SELECT 4, 'A2', 'B1', 'C2' FROM dual UNION ALL
SELECT 5, 'A1', 'B2', 'C2' FROM dual
)
SELECT
id,
A,
COUNT(*) OVER (PARTITION BY A) cnt_A,
B,
COUNT(*) OVER (PARTITION BY B) cnt_B,
C,
COUNT(*) OVER (PARTITION BY C) cnt_C
FROM yourTable
ORDER BY id;
| ID | A | CNT_A | B | CNT_B | C | CNT_C |
|---|---|---|---|---|---|---|
| 1 | A1 | 4 | B1 | 3 | C1 | 2 |
| 2 | A1 | 4 | B2 | 2 | C1 | 2 |
| 3 | A1 | 4 | B1 | 3 | C2 | 3 |
| 4 | A2 | 1 | B1 | 3 | C2 | 3 |
| 5 | A1 | 4 | B2 | 2 | C2 | 3 |