꿈꾸는 개발자, DBA 커뮤니티 구루비

본부별 지점별 매출 집계

by 구사일생 [SQL Query] 매출집계 ROLLUP [2025.03.16 17:35:39]

비슷한 질문들을 하게 되는 듯 합니다.

데이터가 모두 존재 하고 상위코드별로 집계만 하는 경우는 지난번 문의 했던 팁 ROLLUP을 하면 될 듯 한데

INSERT를 하면서 집계를 같이 해야 하는 상황입니다.

물론 지점정보 테이블에 상위 부서 코드는 존재 하나 매년(2번 이상도) 지점코드의 상위 부서가 변동되는 상황입니다.

2개 이상으로 배치가 나눠져 있는 상황을 하나로 합치고 싶습니다.

확인 부탁드립니다.

최종 결과

부서코드	지점코드	상품코드	금액
A001	A001001	K0001	100000
A001	A001001	K0002	110000
A001	A001002	K0001	15000
A001	A001002	K0002	12000
A001		K0001	115000
A001		K0002	122000
B001	B001001	K0001	10000
B001	B001001	K0002	11000
B001	B001002	K0001	15000
B001	B001002	K0002	12000
B001		K0001	25000
B001		K0002	23000

INSERT INTO BBB
SELECT

H_ORGCD
, ORGCD
, PD_CD
, AMT
FROM AAA
WHERE ORG_CD = 'A001001' --지점별
;

INSERT INTO BBB
SELECT
'A001' H_ORGCD
, '' AS ORGCD
, PD_CD
, SUM(AMT) AS AMT
FROM AAA
WHERE ORG_CD IN ('A001001','A001002')
GROUP BY PD_CD

by Hinori_ [2025.03.17 10:28:56]

-- MSSQL 기준
WITH DT AS (
SELECT 'A001001' AS 지점코드, 'K0001' AS 상품코드, 100000 AS 금액 UNION ALL
SELECT 'A001001', 'K0002', 110000 UNION ALL
SELECT 'A001002', 'K0001', 15000 UNION ALL
SELECT 'A001002', 'K0002', 12000 UNION ALL
SELECT 'B001001', 'K0001', 10000 UNION ALL
SELECT 'B001001', 'K0002', 11000 UNION ALL
SELECT 'B001002', 'K0001', 15000 UNION ALL
SELECT 'B001002', 'K0002', 12000 
)
SELECT 부서코드
, 지점코드
, COALESCE(상품코드, SUBSTRING(기준코드, LEN(부서코드) + 1, LEN(기준코드) ) ) AS 상품코드
, SUM(금액) AS 금액
FROM (
SELECT LEFT(DT.지점코드, 4) AS 부서코드
, DT.*
, (LEFT(DT.지점코드, 4) + 상품코드) AS 기준코드
FROM DT
) AS DT_RESULT
GROUP BY ROLLUP(기준코드, 부서코드, 지점코드, 상품코드)
HAVING (부서코드 IS NOT NULL AND 지점코드 IS NOT NULL AND 상품코드 IS NOT NULL) 
OR     (부서코드 IS NOT NULL AND 지점코드 IS NULL     AND 상품코드 IS NULL)
ORDER BY 부서코드, (CASE WHEN 지점코드 IS NULL THEN 1 ELSE 0 END), 상품코드

by Hinori_ [2025.03.17 10:48:54]

보다 맞는 쿼리를 위해선 실제 테이블의 정보가 있어야 합니다.

by 마농 [2025.03.17 11:56:46]

WITH aaa AS
(
SELECT 'A001' h_orgcd, 'A001001' orgcd, 'K0001' pd_cd, 100000 amt
UNION ALL SELECT 'A001', 'A001001', 'K0002', 110000
UNION ALL SELECT 'A001', 'A001002', 'K0001',  15000
UNION ALL SELECT 'A001', 'A001002', 'K0002',  12000
UNION ALL SELECT 'B001', 'B001001', 'K0001',  10000
UNION ALL SELECT 'B001', 'B001001', 'K0002',  11000
UNION ALL SELECT 'B001', 'B001002', 'K0001',  15000
UNION ALL SELECT 'B001', 'B001002', 'K0002',  12000
)
SELECT h_orgcd
     , orgcd
     , pd_cd
     , SUM(amt) amt
  FROM aaa
 GROUP BY h_orgcd, pd_cd, ROLLUP(orgcd)
;

by Hinori_ [2025.03.17 12:48:06]

이게 훨 깔끔하네요 ㅋㅋㅋㅋ

-- MSSQL 기준
WITH DT AS (
SELECT 'A001001' AS 지점코드, 'K0001' AS 상품코드, 100000 AS 금액 UNION ALL
SELECT 'A001001', 'K0002', 110000 UNION ALL
SELECT 'A001002', 'K0001', 15000 UNION ALL
SELECT 'A001002', 'K0002', 12000 UNION ALL
SELECT 'B001001', 'K0001', 10000 UNION ALL
SELECT 'B001001', 'K0002', 11000 UNION ALL
SELECT 'B001002', 'K0001', 15000 UNION ALL
SELECT 'B001002', 'K0002', 12000 
)
SELECT 부서코드
     , 지점코드
     , 상품코드
     , SUM(금액) AMT
  FROM (
SELECT LEFT(DT.지점코드, 4) AS 부서코드
, DT.*
FROM DT
) AS DT_RESULT
 GROUP BY 부서코드, 상품코드, ROLLUP(지점코드)
ORDER BY 부서코드, (CASE WHEN 지점코드 IS NULL THEN 1 ELSE 0 END), 상품코드

by 구사일생 [2025.03.17 21:53:47]

야근 하고 이제야 봤습니다.

제가 억지로 여러번 읽어서 만들었는데 답변 주신 팁으로 수정을 해야 겠네요

그리고 금일 작업을 해 보다가 한가지 더 질문이 생겼습니다.

마농님께서 작성 해 주신 소스 중에 지점명과 상위 부서명을 가져오려면

같은 테이블에 정보가 있어도 2번 읽을수 밖에 없나요? 금일 그걸로 고민하다가 걍 2번 읽었거든요

아래 와 비슷하게(?) 작성 했습니다.

혹시 이것도 팁들이 있으시면 확인 부탁드립니다.

WITH aaa AS
(
SELECT 'A001' h_orgcd, 'A001001' orgcd, 'K0001' pd_cd, 100000 amt
UNION ALL SELECT 'A001', 'A001001', 'K0002', 110000
UNION ALL SELECT 'A001', 'A001002', 'K0001', 15000
UNION ALL SELECT 'A001', 'A001002', 'K0002', 12000
UNION ALL SELECT 'B001', 'B001001', 'K0001', 10000
UNION ALL SELECT 'B001', 'B001001', 'K0002', 11000
UNION ALL SELECT 'B001', 'B001002', 'K0001', 15000
UNION ALL SELECT 'B001', 'B001002', 'K0002', 12000
)
SELECT a.h_orgcd
, c.orgnm
, A.orgcd
, B.orgnm
, A.pd_cd
, SUM(A.amt) amt
FROM aaa A
LEFT OUTER JOIN 조직 B ON A.orgcd = B.ORGCD
LEFT OUTER JOIN 조직 c ON A.h_orgcd = B.ORGCD
GROUP BY a.h_orgcd, c.orgnm, ROLLUP(orgcd)

by 마농 [2025.03.18 13:39:12]

네. 두번 조인하시면 됩니다.
한번만 읽어도 되는 걸 여러번 읽는 다면 개선이 필요한 사항이지만
두번 읽어야 하는 것을 억지로 한번만 읽도록 만드는 것은 개선이 아닙니다.

WITH aaa AS
(
SELECT 'A001' h_orgcd, 'A001001' orgcd, 'K0001' pd_cd, 100000 amt
UNION ALL SELECT 'A001', 'A001001', 'K0002', 110000
UNION ALL SELECT 'A001', 'A001002', 'K0001',  15000
UNION ALL SELECT 'A001', 'A001002', 'K0002',  12000
UNION ALL SELECT 'B001', 'B001001', 'K0001',  10000
UNION ALL SELECT 'B001', 'B001001', 'K0002',  11000
UNION ALL SELECT 'B001', 'B001002', 'K0001',  15000
UNION ALL SELECT 'B001', 'B001002', 'K0002',  12000
)
, 조직 AS
(
SELECT 'A001' orgcd, 'A1처' orgnm
UNION ALL SELECT 'A001001', 'A1팀'
UNION ALL SELECT 'A001002', 'A2팀'
UNION ALL SELECT 'B001'   , 'B1처'
UNION ALL SELECT 'B001001', 'B1팀'
UNION ALL SELECT 'B001002', 'B2팀'
)
SELECT a.h_orgcd
     , b.orgnm h_orgnm
     , a.orgcd
     , c.orgnm
     , a.pd_cd
     , SUM(a.amt) amt
  FROM aaa a
  LEFT OUTER JOIN 조직 b
    ON b.orgcd = a.h_orgcd
  LEFT OUTER JOIN 조직 c
    ON c.orgcd = a.orgcd
 GROUP BY a.h_orgcd, b.orgnm, a.pd_cd, ROLLUP((a.orgcd, c.orgnm))
 ORDER BY a.h_orgcd, GROUPING(a.orgcd), a.orgcd, a.pd_cd
;

by 구사일생 [2025.03.18 21:58:42]

오늘도 이제야 퇴근 후 로그인 했습니다.

그렇군요 항상 조언 듣고 보고 잘 배우고 갑니다.

그럼, 또 문의 사항 있을 때 올리겠습니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

Rich Text Editor, commentnote

Editor toolbars

Press ALT 0 for help

◢Elements path

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변