[mysql] 통계쿼리, WITH ROLLUP 사용하기

통계 쿼리 작성 후 각각 수를 더하는 걸 간단하게 WITH ROLLUP을 사용해서 완성하였다

SELECT
	IFNULL(depart_nm,'모든합계') as depart_nm, 
	IFNULL(org_nm, '전체') as org_nm,
	SUM(X.JOIN_COUNT1) AS stepB,
	SUM(X.JOIN_COUNT2) AS stepC, 
	SUM(X.JOIN_COUNT3) AS stepD, 
	SUM(X.JOIN_COUNT4) AS stepE, 
	SUM(X.JOIN_COUNT5) AS stepF,
	SUM(X.JOIN_COUNT6) AS stepG,
	SUM(X.JOIN_COUNT7) AS stepH,
	SUM(X.JOIN_COUNT8) AS stepI,
	SUM(X.JOIN_COUNT9) AS stepJ,
	SUM(X.JOIN_COUNT10) AS stepK
FROM 
	( SELECT (SELECT org_nm FROM tn_orginfo WHERE org_idx = D.upper_org_idx) as depart_nm, org_nm, 
		CASE WHEN B.step_seq='B' THEN 1 ELSE 0 END JOIN_COUNT1, 
		CASE WHEN B.step_seq='C' THEN 1 ELSE 0 END JOIN_COUNT2,
		CASE WHEN B.step_seq='D' THEN 1 ELSE 0 END JOIN_COUNT3, 
		CASE WHEN B.step_seq='E' THEN 1 ELSE 0 END JOIN_COUNT4, 
		CASE WHEN B.step_seq='F' THEN 1 ELSE 0 END JOIN_COUNT5, 
		CASE WHEN B.step_seq='G' THEN 1 ELSE 0 END JOIN_COUNT6,
		CASE WHEN B.step_seq='H' THEN 1 ELSE 0 END JOIN_COUNT7, 
		CASE WHEN B.step_seq='I' THEN 1 ELSE 0 END JOIN_COUNT8, 
		CASE WHEN B.step_seq='J' THEN 1 ELSE 0 END JOIN_COUNT9, 
		CASE WHEN B.step_seq='K' THEN 1 ELSE 0 END JOIN_COUNT10
	FROM 
		biz_info A
		INNER JOIN biz_info_state B
		ON A.info_idx = B.info_idx
		INNER JOIN biz_use_at C
		ON A.info_idx = C.info_idx
		INNER JOIN tn_orginfo D
		ON A.org_idx = D.org_idx
		AND org_depth = 2
		AND org_use_at != 'N'
	WHERE
		use_at != 'N'
		AND info_state_use_at != 'N'
		AND
		(
		CASE
			WHEN B.step_seq = 'B' THEN use_at
			WHEN B.step_seq = 'C' THEN service_at
			WHEN B.step_seq = 'D' THEN cont_at
			WHEN B.step_seq = 'E' THEN promo_at
			WHEN B.step_seq = 'F' THEN recruit_at
			WHEN B.step_seq = 'G' THEN selection_at
			WHEN B.step_seq = 'H' THEN select_com_at
			WHEN B.step_seq = 'I' THEN propel_at
			WHEN B.step_seq = 'J' THEN propel_com_at
			WHEN B.step_seq = 'K' THEN cs_at
		END
		) != 'N'
	) X 
GROUP BY X.depart_nm, X.org_nm

쿼리 결과

각부서와 팀 별로 잘 나오는 것을 확인 여기서 각 부서의 합계와 총합을 구하는 WITH ROLLUP을 추가하였음.
하단에 WITH ROLLUP만 추가하여

 

총 합과 각 부서별 합계를 구하였다.

 

원래는 '전체'자리에 NULL이 나오지만 IFNULL을 사용하여 원하는 문구로 변경하였음.

 

 

 

## 수정

 

기존쿼리에 팀의 모든 단계가 0일경우 나오지 않는 문제가 발생하여 left outer join추가하여 해결하였다.

처음 시도한 것은 제일 밖에서 leftouter조인을 걸어 보았지만 값은 제대로 나오지만 rollup이 원하는대로 나오지 않아 안쪽으로 leftouter 조인을 추가하는 것으로 변경하였다.

 

SELECT
			IFNULL(depart_nm,'모든합계') as depart_nm, 
			IFNULL(org_nm, '전체') as org_nm,
			SUM(X.JOIN_COUNT1) AS stepB,
			SUM(X.JOIN_COUNT2) AS stepC, 
			SUM(X.JOIN_COUNT3) AS stepD, 
			SUM(X.JOIN_COUNT4) AS stepE, 
			SUM(X.JOIN_COUNT5) AS stepF,
			SUM(X.JOIN_COUNT6) AS stepG,
			SUM(X.JOIN_COUNT7) AS stepH,
			SUM(X.JOIN_COUNT8) AS stepI,
			SUM(X.JOIN_COUNT9) AS stepJ,
			SUM(X.JOIN_COUNT10) AS stepK
		FROM 
			(
				SELECT A.depart_nm, A.org_nm, 
				CASE WHEN B.step_seq='B' THEN 1 ELSE 0 END JOIN_COUNT1, 
				CASE WHEN B.step_seq='C' THEN 1 ELSE 0 END JOIN_COUNT2,
				CASE WHEN B.step_seq='D' THEN 1 ELSE 0 END JOIN_COUNT3, 
				CASE WHEN B.step_seq='E' THEN 1 ELSE 0 END JOIN_COUNT4, 
				CASE WHEN B.step_seq='F' THEN 1 ELSE 0 END JOIN_COUNT5, 
				CASE WHEN B.step_seq='G' THEN 1 ELSE 0 END JOIN_COUNT6,
				CASE WHEN B.step_seq='H' THEN 1 ELSE 0 END JOIN_COUNT7, 
				CASE WHEN B.step_seq='I' THEN 1 ELSE 0 END JOIN_COUNT8, 
				CASE WHEN B.step_seq='J' THEN 1 ELSE 0 END JOIN_COUNT9, 
				CASE WHEN B.step_seq='K' THEN 1 ELSE 0 END JOIN_COUNT10
			FROM 
				(SELECT 
					(SELECT org_nm FROM tn_orginfo WHERE org_use_at != 'N' AND org_idx = b.upper_org_idx) as depart_nm,a.org_nm 
				FROM 
					tn_orginfo a
					JOIN tn_orginfo b
					ON a.org_idx = b.org_idx
				WHERE a.org_use_at != 'N'
					AND a.org_depth=2
					<if test='upperOrgIdx != null and !upperOrgIdx.equals("")'>
						AND a.upper_org_idx = #{upperOrgIdx}
					</if>
					<if test='orgIdx != null and !orgIdx.equals("")'>
						AND a.org_idx = #{orgIdx}
					</if>
		            ) A
				LEFT OUTER JOIN
				(
				SELECT
					(SELECT org_nm FROM tn_orginfo WHERE org_idx = D.upper_org_idx) as depart_nm, org_nm, upper_org_idx, B.step_seq
				FROM
					biz_info A
					INNER JOIN biz_info_state B
					ON A.info_idx = B.info_idx
					INNER JOIN biz_use_at C
					ON A.info_idx = C.info_idx
					INNER JOIN tn_orginfo D
					ON A.org_idx = D.org_idx
					AND org_depth = 2
					AND org_use_at != 'N'
				WHERE
					use_at != 'N'
					AND info_state_use_at != 'N'
					AND
					(
					CASE
						WHEN B.step_seq = 'B' THEN use_at
						WHEN B.step_seq = 'C' THEN service_at
						WHEN B.step_seq = 'D' THEN cont_at
						WHEN B.step_seq = 'E' THEN promo_at
						WHEN B.step_seq = 'F' THEN recruit_at
						WHEN B.step_seq = 'G' THEN selection_at
						WHEN B.step_seq = 'H' THEN select_com_at
						WHEN B.step_seq = 'I' THEN propel_at
						WHEN B.step_seq = 'J' THEN propel_com_at
						WHEN B.step_seq = 'K' THEN cs_at
					END
					) != 'N'
					<choose>
						<when test='sEddate != null and !sEddate.equals("") and sStdate != null and !sStdate.equals("")'>
							<![CDATA[
							AND date_format(info_st_date,'%Y-%m-%d') <= #{sEddate}
					        AND date_format(info_ed_date,'%Y-%m-%d') >= #{sStdate}
					        ]]>
						</when>
						<when test='sStdate != null and !sStdate.equals("")'>
							<![CDATA[
							AND date_format(info_st_date,'%Y-%m-%d') <= #{sStdate}
					        AND date_format(info_ed_date,'%Y-%m-%d') >= #{sStdate}
					        ]]>
						</when>
						<when test='sEddate != null and !sEddate.equals("")'>
							<![CDATA[
							AND date_format(info_st_date,'%Y-%m-%d') <= #{sEddate}
					        AND date_format(info_ed_date,'%Y-%m-%d') >= #{sEddate}
					        ]]>
						</when>
					</choose>
				) B
				ON A.org_nm = B.org_nm
			) X 
		GROUP BY X.depart_nm, X.org_nm 
		WITH ROLLUP

left outer join을 할때는 where절이 아닌 on절에 조건을 추가해야하는것을 유의하자.