Ashish B Sathish Ashish B Sathish - 5 months ago 11
SQL Question

How to partition based on two columns in Oracle/sql

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.

MT0 MT0
Answer

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 |