How to partition based on two columns in Oracle/sql

Question

Please help me with the following

Question:

+------+----------+
| Name | Sub-name |
+------+----------+
| A    | x        |
| A    | x        |
| B    | x        |
| A    | y        |
| B    | y        |
+------+----------+

Desired Result:

+------+----------+-------+
| Name | Sub-name | Count |
+------+----------+-------+
| A    | x        |     2 |
| A    | x        |     2 |
| B    | x        |     1 |
| A    | y        |     1 |
| B    | y        |     1 |
+------+----------+-------+

Three columns Name, Subname, Count

I want to partition based on both name and subname.

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test ( Name, "Sub-name" ) AS
          SELECT 'A', 'x' FROM DUAL
UNION ALL SELECT 'A', 'x' FROM DUAL
UNION ALL SELECT 'B', 'x' FROM DUAL
UNION ALL SELECT 'A', 'y' FROM DUAL
UNION ALL SELECT 'B', 'y' FROM DUAL;

Query 1:

SELECT Name,
       "Sub-name",
       COUNT( 1 ) OVER ( PARTITION BY "Sub-name", Name ) AS "Count"
FROM   test

Results:

| NAME | Sub-name | Count |
|------|----------|-------|
|    A |        x |     2 |
|    A |        x |     2 |
|    B |        x |     1 |
|    A |        y |     1 |
|    B |        y |     1 |