CODEWITHSUNDEEP
sqloracle-databasewindow-functions
Ashish B Sathish
Ashish B Sathish

Reputation: 121

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.

Upvotes: 4

Views: 30917

Answers (3)

pari elanchezhiyan
pari elanchezhiyan

Reputation: 1

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....

Upvotes: 0

MT0
MT0

Reputation: 167972

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 |

Upvotes: 9

chris
chris

Reputation: 141

Try this:

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

Upvotes: 1

Related Questions