Oracle: finding duplicate rows in a selected query

Question

My SQL query returns the results with 4 columns "A", "B", "C", "D".

Suppose the results are:

ABCD 1 1 1 1 1 1 1 2 2 2 2 1 

Is it possible to get the number of repeating rows with columns "A", "B", "C" in each row.

eg. Expected Result:

 ABCD cnt 1 1 1 1 2 1 1 1 2 2 2 2 2 1 1 

I tried using count (*). But it returns me the total number of rows returned by the request. Other information is that in the example I mentioned only 3 columns, based on which I need to check the score. But my actual query has 8 columns. And the number of rows in the database is huge. Therefore, I think that a group would not be an option here. Any hint is noticeable. Thanks.

Answer 1

It may be too late, but probably counting as an analytic function (like a window function) in oracle will help you. When I understand your request correctly, this should solve your problem:

 create table sne_test(a number(1) ,b number(1) ,c number(1) ,d number(1) ,e number(1) ,f number(1)); insert into sne_test values(1,1,1,1,1,1); insert into sne_test values(1,1,2,1,1,1); insert into sne_test values(1,1,2,4,1,1); insert into sne_test values(1,1,2,5,1,1); insert into sne_test values(1,2,1,1,3,1); insert into sne_test values(1,2,1,2,1,2); insert into sne_test values(2,1,1,1,1,1); commit; SELECT a,b,c,d,e,f, count(*) over (PARTITION BY a,b,c) FROM sne_test; ABCDEF AMOUNT -- -- -- -- -- -- ------ 1 1 1 1 1 1 1 1 1 2 4 1 1 3 1 1 2 1 1 1 3 1 1 2 5 1 1 3 1 2 1 1 3 1 2 1 2 1 2 1 2 2 2 1 1 1 1 1 1 

Answer 2

To find duplicates, you must group the data based on the key column.

 select count(*) ,empno from emp group by empno having count(*) > 1; 

This allows you to aggregate with empno even if for each category there are several records (more than one).

Answer 3

You should use a subquery in which you get the number of rows grouped by A, B and C. And then you attach this subquery again to your table (or to your query), for example:

 select your_table.A, your_table.B, your_table.C, your_table.D, cnt from your_table inner join (SELECT A, B, C, count(*) as cnt FROM your_table GROUP BY A, B, C) t on tA = your_table.A and tB = your_table.B and tC = your_table.C