지역별 사람수 구하는 쿼리

오라클에서 사용하는 쿼리임 ms-sql 은 nvl 대신 isnull , sugstr 대신 substring 사용하면 가능
SELECT
COUNT(*) as tot,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '서울' THEN 1 END),0) as sol,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '광주' THEN 1 END),0) as kju,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '대구' THEN 1 END),0) as dgu,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '대전' THEN 1 END),0) as djn,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '부산' THEN 1 END),0) as bsn,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '울산' THEN 1 END),0) as usn,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '인천' THEN 1 END),0) as icn,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '경기' THEN 1 END),0) as kki,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '강원' THEN 1 END),0) as kwn,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '경남' THEN 1 END),0) as knm,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '경북' THEN 1 END),0) as kbk,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '전남' THEN 1 END),0) as jnm,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '전북' THEN 1 END),0) as jbk,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '충남' THEN 1 END),0) as cnm,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '충북' THEN 1 END),0) as cbk,
NVL(COUNT(CASE WHEN
SUBSTR(basc_addr,1,2) = '제주' THEN 1 END),0) as jju,
(COUNT(*) - SUM(NVL2(basc_addr,1,0))) as etc
FROM mti_user
//basc_addr : 지역 명칭이 들어있는 컬럼
// (COUNT(*) - SUM(NVL2(basc_addr,1,0))) as etc : 전체갯수에서 지역별 값을 제외한 부분 (null이나 오타로인한 데이터 갯수)