Count 내 조건을 적용하기

조회 된 데이터 내 CODE_VAL 의 값이 2인 경우의 카운트 값을 가져오기 위해 사용한 쿼리.

Oracle [ Count 함수, Decode 함수 사용 ]

COUNT(DECODE(컬럼명, 조건, '참일때','거짓일때'))

     SELECT COUNT(DECODE(CODE_VAL,'2','1')) TRANS_S_COUNT, COUNT(DECODE(CODE_VAL,'3','1')) TRANS_C_COUNT FROM     (     SELECT A.TRANS_NO, A.REG_USER_NO, A.GOODS_AMT, B.CODE_VAL FROM TB_TRANS A, TB_COMMON_CODE B      WHERE A.TRANS_ST = B.CODE_VAL AND B.TARGET_TBL='TB_TRANS'      AND B.TARGET_COL='TRANS_ST' AND A.DEL_YN <> 'Y'     )A     INNER JOIN      (     SELECT USER_ID, USER_NO FROM TB_USER     WHERE USER_ID = 'kang'     )B     ON A.REG_USER_NO = B.USER_NO; 

SELECT Count(Decode(code_val, '2', '1')) TRANS_S_COUNT,
       Count(Decode(code_val, '3', '1')) TRANS_C_COUNT
  FROM   (SELECT A.trans_no,
                 A.reg_user_no,
                 A.goods_amt,
                 B.code_val
            FROM tb_trans A,
                 tb_common_code B
           WHERE A.trans_st = B.code_val
             AND B.target_tbl = 'TB_TRANS'
             AND B.target_col = 'TRANS_ST'
             AND A.del_yn <> 'Y')A
      INNER JOIN (SELECT user_id
                         , user_no
                   FROM tb_user
                  WHERE user_id = 'kang') B
              ON A.reg_user_no = B.user_no;      

Oracle, MySQL, MSSQL, ProstgresSQL 모두 적용 가능 [ CASE WHEN THEN ELSE END ]

CASE WHEN 조건 THEN '대체값' ELSE '기본값' END

     SELECT COUNT(CASE WHEN CODE_VAL='2' THEN '1' END) 
       FROM ( SELECT A.TRANS_NO
                         , A.REG_USER_NO
                       , A.GOODS_AMT
                       , B.CODE_VAL FROM TB_TRANS A
                       , TB_COMMON_CODE B
                 WHERE A.TRANS_ST = B.CODE_VAL 
                   AND B.TARGET_TBL='TB_TRANS'
                   AND B.TARGET_COL='TRANS_ST'
                   AND A.DEL_YN <> 'Y' )A
 INNER JOIN ( SELECT USER_ID
                     , USER_NO 
                FROM TB_USER
               WHERE USER_ID = 'kang'  )B     
         ON A.REG_USER_NO = B.USER_NO;