with t(ids, status) as (
select '001', 0 from dual union all
select '001', 1 from dual union all
select '001', 2 from dual union all
select '001', 3 from dual union all
select '002', 3 from dual union all
select '002', 3 from dual union all
select '003', 0 from dual union all
select '003', 0 from dual)
SELECT ids, case when sum(nvl(status,0)) = 0 then 0
                 when avg(nvl(status,0)) = 3 then 3
                 else 1
            end status
  FROM T
group by ids  ;

혹시 이런형태를 원하신다면

SELECT IDS, CASE WHEN SUM_STATUS = 0 THEN 0 
                 WHEN AVG_STATUS = 3 THEN 3
                 ELSE 1
            END STATUS
  FROM (SELECT IDS
             , STATUS
             , SUM(NVL(STATUS, 0)) OVER(PARTITION BY IDS) SUM_STATUS
             , AVG(NVL(STATUS, 0)) OVER(PARTITION BY IDS) AVG_STATUS
          FROM T);

SELECT IDS
     , DECODE(AVG(STATUS),0,0,3,3,1) STATUS
FROM T
GROUP BY IDS
;

SELECT IDS, DECODE(MAX(STATUS) || MIN(STATUS), '00', '0', '33', '3', '1') STATUS
  FROM T
GROUP BY IDS;