2008. 10. 8. 15:31

[oracle]각각의 합과 총합을 Reporting하는 쿼리

/*  UNION, UNION ALL
    UNION : 중복을 제거한 합집합
    UNION ALL : 모든 합집합         */
 SELECT 1 dummy FROM DUAL
  UNION ALL
 SELECT 2 dummy FROM DUAL

/*  Cartesian Product    */  
 SELECT *
   FROM ( SELECT 1 dummy FROM DUAL
           UNION ALL
    SELECT 2 dummy FROM DUAL
  ) a,
        ( SELECT deptno, count(*) cnt
       FROM emp
     GROUP BY deptno
  ) b
 
/*  각각의 합과 총합을 Reporting    */
 SELECT Decode(a.dummy, 1, Nvl(b.dname, 'Null'), 'Sum'),
        Decode(a.dummy, 1, Max(b.cnt), Sum(b.cnt))
   FROM ( SELECT 1 dummy FROM DUAL
           UNION ALL
    SELECT 2 dummy FROM DUAL
  ) a,
        ( SELECT b1.deptno, dname, count(*) cnt
       FROM emp b1, dept b2
     WHERE b1.deptno = b2.deptno
     GROUP BY b1.deptno, dname
  ) b
  GROUP BY a.dummy, Decode(a.dummy, 1, Nvl(b.dname, 'Null'), 'Sum')
  ORDER BY a.dummy, Decode(a.dummy, 1, Nvl(b.dname, 'Null'), 'Sum')
 
/*  각각의 합과 총합을 Reporting    */ 
SELECT a.sys_name 검사항목,
       sum(a.cnt)  총갯수,
       sum(decode(a.stat, '01', cnt, 0)) 운용,
       sum(decode(a.stat, '03', cnt, 0)) 예비운용,
       NULL 최종연동시작시간,
       NULL 최종연동완료시간
  FROM ( SELECT 'iNetPost' sys_name,
                stat,
                count(*) cnt
           FROM tb_office@np_link
          WHERE stat IN ('01', '03')
          GROUP BY stat
       ) a
 GROUP BY a.sys_name