drop table tab_1;
create table tab_1(
ReadInterval Number(3),
StripNo Number(5),
Width Number(5),
Decision Varchar2(10)
);
insert into tab_1 values ( 1, 1, 7, 'A' );
insert into tab_1 values ( 2, 1, 8, 'A' );
insert into tab_1 values ( 3, 1, 9, 'A' );
insert into tab_1 values ( 4, 1, 4, 'R' );
insert into tab_1 values ( 5, 2, 5, 'R' );
insert into tab_1 values ( 6, 2, 7, 'R' );
insert into tab_1 values ( 7, 3, 8, 'A' );
insert into tab_1 values ( 8, 3, 8, 'A' );
select
a.stripno stripno,
decode( a.num_records, nvl(b.num_accepts,0), 'ACCEPTED', 'REJECTED') status
from (
select stripno, count(*) num_records from tab_1
group by stripno
) a,
(
select stripno, count(*) num_accepts from tab_1
where decision = 'A'
group by stripno
) b
where a.stripno = b.stripno (+);
STRIPNO STATUS
---------- --------
1 REJECTED
2 REJECTED
3 ACCEPTED