SELECT ref_type
     , COUNT(*) cnt
     , COUNT(return_cnt) return_cnt
  FROM (SELECT CASE WHEN first_visit_ref_url LIKE 'search.%'
                      OR first_visit_ref_url LIKE ' http://search.%'
                      OR first_visit_ref_url LIKE '%www.google.co.kr/search?%'
                    THEN '검색엔진'
                    WHEN first_visit_ref_url LIKE 'http://127.0.0.1%'
                      OR first_visit_ref_url LIKE '127.0.0.1%'
                    THEN '직접입력'
                    WHEN first_visit_ref_url LIKE '%mail2.daum.net%'      --다음
                      OR first_visit_ref_url LIKE '%mail.naver.com%'      --네이버
                      OR first_visit_ref_url LIKE '%mail.yahoo%'          --야후
                      OR first_visit_ref_url LIKE '%mail.dreamwiz%'       --드림위즈
                      OR first_visit_ref_url LIKE '%mail.korea%'          --코리아닷컴
                      OR first_visit_ref_url LIKE '%mail.lycos%'          --라이코스
                      OR first_visit_ref_url LIKE '%netian.com/mailview%' --네띠앙
                      OR first_visit_ref_url LIKE '%kebi.com/mbody%'      --깨비
                      OR first_visit_ref_url LIKE '%mail.chol%'           --천리안
                      OR first_visit_ref_url LIKE '%mail.live.com%'       --핫메일
                      OR first_visit_ref_url LIKE '%mail.nate%'           --네이트
                      OR first_visit_ref_url LIKE '%gmail.google.com%'    --구글
                    THEN '이메일'
                    ELSE '웹사이트' END AS ref_type
             , CASE WHEN (SELECT COUNT(*)
                            FROM ci_visitor_actions b
                           WHERE b.id_session = a.id_session
                          ) = 1
                    THEN '반송' END AS return_cnt
          FROM ci_visitor_info a
         WHERE a.visit_time BETWEEN TO_DATE ( '2014/01/21' , 'YYYY/MM/DD' )
                                AND TO_DATE ( '2014/01/21' , 'YYYY/MM/DD' ) + 0.99999
           AND a.id_site = 1
           AND a.visit_count != 1
        )
 GROUP BY ref_type
;