Oracle counting number of SAME and DIFFERENT occurrences-CodePudding

I have a table that contains two CLOBS, which I'm in the process of redesigning to contain 1 clob per row.

I have a query below that identifies if the CLOBS are the same or not, which is working fine.

I can use some help with a query, which summarizes the number of SAME and DIFFERENT columns.

I'm looking to produce an output as follows:

Total_rows  same_cnt different_cnt
3     1   2

Any help would be greatly appreciated. Thanks to all who answer.



CREATE table table_x(
seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
val NUMBER, 
clob1 CLOB,
clob2 CLOB);


insert into table_x (val, clob1, clob2) values (1,'aaaaaaaaaa','aaaaaaaaaa');

insert into table_x (val, clob1, clob2) values (1,'aaaaa','aaaaaaaaaa');

insert into table_x (val, clob1, clob2) values (2,'Xaaaaaaaaa','aaaaaaaaaa');


SELECT x.*,
       CASE DBMS_LOB.compare (x.clob1, x.clob2) WHEN 0 THEN 'SAME' ELSE 'DIFFERENT' END    AS comparison
  FROM table_x x;

SEQ_NUM    VAL    CLOB1    CLOB2    COMPARISON
1    1    aaaaaaaaaa    aaaaaaaaaa    SAME
2    1    aaaaa    aaaaaaaaaa    DIFFERENT
3    2    Xaaaaaaaaa    aaaaaaaaaa    DIFFERENT

CodePudding user response：

You can use a conditional SUM as follows

with dt as (
SELECT x.*,
       CASE DBMS_LOB.compare (x.clob1, x.clob2) WHEN 0 THEN 'SAME' ELSE 'DIFFERENT' END    AS comparison
  FROM table_x x)
select 
count(*) total,
sum(case when comparison = 'DIFFERENT' then 1 else 0 end) as cnt_different,
sum(case when comparison = 'SAME' then 1 else 0 end) as cnt_same
from dt

which returns

     TOTAL CNT_DIFFERENT   CNT_SAME
---------- ------------- ----------
         3             2          1