SELECT x.dt, x.ch, x.cd
     , y.me_1
     , COUNT(DECODE(z.me,y.me_1,1)) me_1_cnt
     , y.me_2
     , COUNT(DECODE(z.me,y.me_2,1)) me_2_cnt
     , y.me_3
     , COUNT(DECODE(z.me,y.me_3,1)) me_3_cnt
  FROM (SELECT a.dt, b.ch, c.cd
          FROM (SELECT DISTINCT dt FROM t_db) a
             , (SELECT codenm ch FROM t_syscode WHERE grcode = ’11’) b
             , (SELECT codenm cd FROM t_syscode WHERE grcode = ’31’) c
        ) x
     , (SELECT MIN(DECODE(code,1,codenm)) me_1
             , MIN(DECODE(code,2,codenm)) me_2
             , MIN(DECODE(code,3,codenm)) me_3
          FROM t_syscode
         WHERE grcode = ’21’
        ) y
     , t_db z
 WHERE x.dt = z.dt(+)
   AND x.ch = z.ch(+)
   AND x.cd = z.cd(+)
 GROUP BY x.dt, x.ch, x.cd, y.me_1, y.me_2, y.me_3