Oracle SQL - DISTINCT with count of supressed rows

When I use the DISTINCT clause in sql, how can I add one last column with the number of the rows that were "suppressed" in just one (because they are equal).

Solution

You can't do that directly with distinct. You could add an analytic count() in a subquery that then has distinct applied, but it's simpler to use an aggregate count() and a group by instead. You would have to include all the existing select-list items in the group by clause.

So if you had a table like this:

create table t42 (col1 number, col2 varchar2(10));
insert into t42 values (42, 'AAA');
insert into t42 values (42, 'AAA');
insert into t42 values (42, 'BBB');
insert into t42 values (42, 'BBB');
insert into t42 values (42, 'BBB');
insert into t42 values (43, 'AAA');

And you're currently doing this:

select distinct col1, col2
from t42
order by col1, col2;

      COL1 COL2     
---------- ----------
        42 AAA        
        42 BBB        
        43 AAA

To get the number of duplicates you can do:

select col1, col2, count(*)
from t42
group by col1, col2
order by col1, col2;

      COL1 COL2         COUNT(*)
---------- ---------- ----------
        42 AAA                 2 
        42 BBB                 3 
        43 AAA                 1