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 |

Answer 2

Try this:

select name, sub_name, count(name) over (partition by name, sub_name) as count from table

Answer 3

select ra.Name,ra.sub-name,ta.count from table ra inner join (select Name,sub-name,count(*) from table group by Name,sub-name)ta on ra.Name=ta.Name on ra.sub-name=ta.sub-name order by sub-name desc

Really i don't understand why we need to use partition for this solution.Even the above join query works fine...hope it should....