By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table source_table1 (invnum, invamount,descr,linetype, amount, linenumber) as
select 123,120,'desc1', 'ITEM', 100, 1 from dual union all
select 123,120,'desc1', 'TAX' , 20, 2 from dual union all
select 446,220,'desc2', 'ITEM', 100, 1 from dual union all
select 446,220,'desc2', 'ITEM', 100, 2 from dual union all
select 446, 220,'desc22','TAX' , 20, 3 from dual union all
select 500, 220,'desc3','ITEM' , 220, 1 from dual;
6 rows affected
-- original
select count(1)
from (
select count(invnum)
from (
select invnum,count(*)
from source_table1
group by invnum, invamount,descr
)
group by invnum
having count(invnum) > 1
)
| COUNT(1) |
|---|
| 1 |
-- proposed
SELECT count(DISTINCT invnum)
FROM source_table1
GROUP BY invnum
HAVING count(DISTINCT descr) > 1
| COUNT(DISTINCTINVNUM) |
|---|
| 1 |