지역별 집계

일자별로 집계가 필요한경우

DECLARE @조회시작일자 datetime
      , @조회종료일자 datetime

SET @조회시작일자 = '2004-06-14'
SET @조회종료일자 = '2004-06-18'
 
SELECT isnull(count(case when substring(address,1,2) = '서울' then 1 end),0) as 서울
     , isnull(count(case when substring(address,1,2) = '경기' then 1 end),0) as 경기
     , isnull(count(case when substring(address,1,2) = '인천' then 1 end),0) as 인천
     , isnull(count(case when substring(address,1,2) = '대전' then 1 end),0) as 대전
     , isnull(count(case when substring(address,1,2) = '대구' then 1 end),0) as 대구
     , isnull(count(case when substring(address,1,2) = '부산' then 1 end),0) as 부산
     , isnull(count(case when substring(address,1,2) = '울산' then 1 end),0) as 울산
     , isnull(count(case when substring(address,1,2) = '강원' then 1 end),0) as 강원
     , isnull(count(case when substring(address,1,2) = '광주' then 1 end),0) as 광주
     , isnull(count(case when substring(address,1,2) = '충북' then 1 end),0) as 충북
     , isnull(count(case when substring(address,1,2) = '충남' then 1 end),0) as 충남
     , isnull(count(case when substring(address,1,2) = '경남' then 1 end),0) as 경남
     , isnull(count(case when substring(address,1,2) = '경북' then 1 end),0) as 경북
     , isnull(count(case when substring(address,1,2) = '전남' then 1 end),0) as 전남
     , isnull(count(case when substring(address,1,2) = '전북' then 1 end),0) as 전북
     , isnull(count(case when substring(address,1,2) = '제주' then 1 end),0) as 제주
     , count(*) as 총합
  FROM TABLE
 WHERE writeday >= convert(char(10),@조회시작일자,120) 
   AND writeday < convert(char(10),dateadd(dd,1,@조회종료일자),120) 
 GROUP BY substring(writeday,1,10)

일자무시하고 해당기간동안의 전체집계만 필요한 경우

DECLARE @조회시작일자 datetime
      , @조회종료일자 datetime

SET @조회시작일자 = '2004-06-14'
SET @조회종료일자 = '2004-06-18'

SELECT isnull(count(case when substring(address,1,2) = '서울' then 1 end),0) as 서울
     , isnull(count(case when substring(address,1,2) = '경기' then 1 end),0) as 경기
     , isnull(count(case when substring(address,1,2) = '인천' then 1 end),0) as 인천
     , isnull(count(case when substring(address,1,2) = '대전' then 1 end),0) as 대전
     , isnull(count(case when substring(address,1,2) = '대구' then 1 end),0) as 대구
     , isnull(count(case when substring(address,1,2) = '부산' then 1 end),0) as 부산
     , isnull(count(case when substring(address,1,2) = '울산' then 1 end),0) as 울산
     , isnull(count(case when substring(address,1,2) = '강원' then 1 end),0) as 강원
     , isnull(count(case when substring(address,1,2) = '광주' then 1 end),0) as 광주
     , isnull(count(case when substring(address,1,2) = '충북' then 1 end),0) as 충북
     , isnull(count(case when substring(address,1,2) = '충남' then 1 end),0) as 충남
     , isnull(count(case when substring(address,1,2) = '경남' then 1 end),0) as 경남
     , isnull(count(case when substring(address,1,2) = '경북' then 1 end),0) as 경북
     , isnull(count(case when substring(address,1,2) = '전남' then 1 end),0) as 전남
     , isnull(count(case when substring(address,1,2) = '전북' then 1 end),0) as 전북
     , isnull(count(case when substring(address,1,2) = '제주' then 1 end),0) as 제주
     , count(*) as 총합
  FROM TABLE
 WHERE writeday >= convert(char(10),@조회시작일자,120) 
   AND writeday < convert(char(10),dateadd(dd,1,@조회종료일자),120)