일자별로 집계가 필요한경우
일자무시하고 해당기간동안의 전체집계만 필요한 경우
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)