database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41483 게시물 읽기
No. 41483
소계,합계 문의 드립니다.
작성자
김민수
작성일
2017-11-20 10:43
조회수
7,291

도저히 해결 능력이 안되서 문의 드립니다.

 

전체 쿼리는 아래와 같습니다.

WITH XX AS (

SELECT RVC_GBN

, GRP_NM

, RVC_CD

, DET_NM

, FOOD

, BEVE

, PRODUCT

, TOT

, SUM(FOOD_COST) AS FOOD_COST

, SUM(BEVE_COST) AS BEVE_COST

, SUM(PRODUCT_COST) AS PRODUCT_COST

, SUM(FOOD_AVG) AS FOOD_AVG

, SUM(BEVE_AVG) AS BEVE_AVG

, SUM(PRODUCT_AVG) AS PRODUCT_AVG

, 0 AS FOOD_INOUT

, 0 AS BEVE_INOUT

, 0 AS PRODUCT_INOUT

, 0 AS FOOD_SOMO_INOUT

, 0 AS BEVE_SOMO_INOUT

, 0 AS PRODUCT_SOMO_INOUT

FROM(

SELECT C.CD_RVC_GBN AS RVC_GBN

, C.GRP_NM

, A.RVC_CD

, C.DET_NM

, SUM(CASE WHEN SALES_GBN = '01' THEN SELL_AMT ELSE 0 END) AS FOOD

, SUM(CASE WHEN SALES_GBN = '02' THEN SELL_AMT ELSE 0 END) AS BEVE

, SUM(CASE WHEN SALES_GBN = '04' THEN SELL_AMT ELSE 0 END) AS PRODUCT

, SUM(CASE WHEN SALES_GBN = '01' THEN SELL_AMT ELSE 0 END) + SUM(CASE WHEN SALES_GBN = '02' THEN SELL_AMT ELSE 0 END) + SUM(CASE WHEN SALES_GBN = '04' THEN SELL_AMT ELSE 0 END) AS TOT

, (CASE WHEN C.ITEM = '10' THEN C.COST1 ELSE 0 END) AS FOOD_COST

, (CASE WHEN C.ITEM = '20' THEN C.COST1 ELSE 0 END) AS BEVE_COST

, (CASE WHEN C.ITEM = '70' THEN C.COST1 ELSE 0 END) AS PRODUCT_COST

, ROUND(DECODE(SUM(CASE WHEN SALES_GBN = '01' THEN SELL_AMT ELSE 0 END),0,0,(CASE WHEN C.ITEM = '10' THEN C.COST1 ELSE 0 END) / SUM(CASE WHEN SALES_GBN = '01' THEN SELL_AMT ELSE 0 END))*100,1) AS FOOD_AVG

, ROUND(DECODE(SUM(CASE WHEN SALES_GBN = '02' THEN SELL_AMT ELSE 0 END),0,0,(CASE WHEN C.ITEM = '20' THEN C.COST1 ELSE 0 END) / SUM(CASE WHEN SALES_GBN = '02' THEN SELL_AMT ELSE 0 END))*100,1) AS BEVE_AVG

, ROUND(DECODE(SUM(CASE WHEN SALES_GBN = '04' THEN SELL_AMT ELSE 0 END),0,0,(CASE WHEN C.ITEM = '70' THEN C.COST1 ELSE 0 END) / SUM(CASE WHEN SALES_GBN = '04' THEN SELL_AMT ELSE 0 END))*100,1) AS PRODUCT_AVG

FROM MC_IF_SALES_CLOSE A

, (SELECT RVC_CD, ITEM, COST1, CD_RVC_GBN, BIZ_SL_CD, GRP_NM, DET_NM

FROM

(SELECT B.BIZ_SL_CD AS RVC_CD

, A.ITEM_GRP2 AS ITEM

, B.CD_RVC_GBN

, B.BIZ_SL_CD

, C.CD_NM_LO AS GRP_NM

, D.BIZ_SL_NM AS DET_NM

, SUM(A.SALES_USE_AMT) AS COST1

FROM MC_STOCK_MONTH A

, MM_MT_SL B

, MM_MT_SYS_CD C

, MM_MT_SL_SALES D

WHERE 1=1

AND A.BRANCH_CD = B.BRANCH_CD

AND A.STR_CD = B.STR_CD

AND B.BRANCH_CD = C.BRANCH_CD

AND B.CD_RVC_GBN = C.CD_ID

AND B.BIZ_SL_CD = D.BIZ_SL_CD

AND A.CLOSE_MM = SUBSTR('20170801', 1, 6)

AND A.ITEM_GRP2 IN ('10','20','70')

AND (A.ITEM_GRP2 = '10' OR A.ITEM_GRP2 = '20' OR A.ITEM_GRP2 = '70')

AND A.STR_CD NOT IN ('HG08092','121157')

 

AND B.INT_SL_TYPE_CD2 IN ('MC1303','MC1304','MC1302')

AND A.BRANCH_CD = 'ICA'

GROUP BY B.STR_CD, A.ITEM_GRP2, B.CD_RVC_GBN, B.BIZ_SL_CD, C.CD_NM_LO, D.BIZ_SL_NM

UNION

SELECT B.BIZ_SL_CD AS RVC_CD

, A.ITEM_GRP2 AS ITEM

, B.CD_RVC_GBN

, B.BIZ_SL_CD

, E.CD_NM_LO AS GRP_NM

, F.BIZ_SL_NM AS DET_NM

, SUM(D.AMT) AS COST1

FROM MC_STOCK_MONTH A

, MM_MT_SL B

, MC_ITEM_MOVE D

, MM_MT_SYS_CD E

, MM_MT_SL_SALES F

WHERE A.BRANCH_CD = B.BRANCH_CD

AND A.STR_CD = B.STR_CD

AND B.BRANCH_CD = E.BRANCH_CD

AND B.CD_RVC_GBN = E.CD_ID

AND B.BIZ_SL_CD = F.BIZ_SL_CD

AND A.CLOSE_MM = SUBSTR('20170801', 1, 6)

AND A.ITEM_GRP2 IN ('10','20','70')

AND (A.ITEM_GRP2 = '10' OR A.ITEM_GRP2 = '20' OR A.ITEM_GRP2 = '70')

AND A.STR_CD NOT IN ('HG08092','121157')

 

AND B.INT_SL_TYPE_CD2 IN ('MC1303','MC1304','MC1302')

AND D.MTRL_CD = A.MTRL_CD(+)

AND D.OUT_STR_CD = A.STR_CD(+)

AND D.ORDER_SLIP_GBN IS NOT NULL

AND A.BRANCH_CD = 'ICA'

AND D.BRANCH_CD = A.BRANCH_CD AND MOVE_DT LIKE SUBSTR('20170801', 1, 6)||'%'

GROUP BY B.STR_CD, A.ITEM_GRP2, B.CD_RVC_GBN, B.BIZ_SL_CD, E.CD_NM_LO, F.BIZ_SL_NM)) C

WHERE 1=1

AND A.RVC_CD = C.RVC_CD(+)

AND (A.SALES_GBN = '01' OR A.SALES_GBN = '02' OR A.SALES_GBN = '04')

AND A.BRANCH_CD = 'ICA'

AND A.SELL_DT BETWEEN '20170801' AND '20170831'

GROUP BY C.CD_RVC_GBN, C.GRP_NM,A.RVC_CD,C.DET_NM,C.ITEM,C.COST1

HAVING SUM(CASE WHEN SALES_GBN = '01' THEN SELL_AMT ELSE 0 END) > 0

OR SUM(CASE WHEN SALES_GBN = '02' THEN SELL_AMT ELSE 0 END) > 0

OR SUM(CASE WHEN SALES_GBN = '04' THEN SELL_AMT ELSE 0 END) > 0

)

GROUP BY RVC_GBN,GRP_NM,RVC_CD,DET_NM,FOOD,BEVE,PRODUCT,TOT

UNION ALL

SELECT RVC_GBN

, GRP_NM

, RVC_CD

, DET_NM

, 0,0,0,0,0,0,0,0,0,0

, SUM(FOOD_INOUT) AS FOOD_INOUT

, SUM(BEVE_INOUT) AS BEVE_INOUT

, SUM(PRODUCT_INOUT) AS PRODUCT_INOUT

, SUM(FOOD_SOMO_INOUT) AS FOOD_SOMO_INOUT

, SUM(BEVE_SOMO_INOUT) AS BEVE_SOMO_INOUT

, SUM(PRODUCT_SOMO_INOUT) AS PRODUCT_SOMO_INOUT

FROM (

SELECT B.REF_V01 AS RVC_GBN,B.GRP_NM,B.RVC_CD,B.DET_NM,

CASE WHEN A.ITEM_GRP2 = '10' THEN SUM(A.BUY_IN_AMT + A.STR_IN_AMT + A.MOVE_IN_AMT - A.BUY_OUT_AMT - A.MOVE_OUT_AMT - A.STR_OUT_AMT - A.INHOUSE_USE_AMT) ELSE 0 END AS FOOD_INOUT,

CASE WHEN A.ITEM_GRP2 = '20' THEN SUM(A.BUY_IN_AMT + A.STR_IN_AMT + A.MOVE_IN_AMT - A.BUY_OUT_AMT - A.MOVE_OUT_AMT - A.STR_OUT_AMT - A.INHOUSE_USE_AMT) ELSE 0 END AS BEVE_INOUT,

CASE WHEN A.ITEM_GRP2 = '70' THEN SUM(A.BUY_IN_AMT + A.STR_IN_AMT + A.MOVE_IN_AMT - A.BUY_OUT_AMT - A.MOVE_OUT_AMT - A.STR_OUT_AMT - A.INHOUSE_USE_AMT) ELSE 0 END AS PRODUCT_INOUT,

CASE WHEN A.ITEM_GRP2 = '10' THEN SUM(A.SALES_USE_AMT + A.SLIP_USE_AMT + A.INHOUSE_USE_AMT + A.LOSS_USE_AMT) ELSE 0 END AS FOOD_SOMO_INOUT,

CASE WHEN A.ITEM_GRP2 = '20' THEN SUM(A.SALES_USE_AMT + A.SLIP_USE_AMT + A.INHOUSE_USE_AMT + A.LOSS_USE_AMT) ELSE 0 END AS BEVE_SOMO_INOUT,

CASE WHEN A.ITEM_GRP2 = '70' THEN SUM(A.SALES_USE_AMT + A.SLIP_USE_AMT + A.INHOUSE_USE_AMT + A.LOSS_USE_AMT) ELSE 0 END AS PRODUCT_SOMO_INOUT

FROM MC_STOCK_MONTH A

, (SELECT MMS.BRANCH_CD, MMS.STR_CD, MSC.CD_ID DET_CD, MMS.BIZ_SL_CD RVC_CD, MMSS.BIZ_SL_NM DET_NM

, MMS.CD_RVC_GBN REF_V01

, MSC.CD_NM_LO GRP_NM

FROM MM_MT_SL MMS

, MM_MT_SYS_CD MSC

, MM_MT_SL_SALES MMSS

WHERE MMS.CD_RVC_GBN = MSC.CD_ID

AND MMS.BRANCH_CD = MSC.BRANCH_CD

AND MMS.BIZ_SL_CD = MMSS.BIZ_SL_CD

AND MMS.INT_SL_TYPE_CD2 IN ('MC1303','MC1304','MC1302')

AND MMS.BRANCH_CD = 'ICA') B

WHERE A.BRANCH_CD = B.BRANCH_CD

 

AND A.STR_CD = B.STR_CD

AND A.CLOSE_MM = SUBSTR('20170801', 1, 6)

AND A.ITEM_GRP2 IN ('10','20','70')

AND (A.ITEM_GRP2 = '10' OR A.ITEM_GRP2 = '20' OR A.ITEM_GRP2 = '70')

AND A.STR_CD NOT IN ('HG08092','121157')

 

AND A.BRANCH_CD = 'ICA'

GROUP BY B.REF_V01,B.GRP_NM,B.RVC_CD,B.DET_NM,A.ITEM_GRP2

)

 

GROUP BY RVC_GBN,GRP_NM,RVC_CD,DET_NM

)

SELECT

RVC_GBN

, GRP_NM

, RVC_CD

, CASE GROUPING_ID(RVC_GBN,GRP_NM,RVC_CD,DET_NM)

WHEN 3 THEN '계'

WHEN 15 THEN '합계'

ELSE DET_NM

END AS DET_NM

, SUM(TOT) AS TOT

, SUM(FOOD_COST + BEVE_COST + PRODUCT_COST) AS M_COST

, ROUND(DECODE(SUM(TOT),0,0, (SUM(FOOD_COST + BEVE_COST + PRODUCT_COST) / SUM(TOT)) * 100),1) AS M_AVG

, SUM(FOOD_INOUT+BEVE_INOUT+PRODUCT_INOUT) AS INOUT

, ROUND(DECODE(SUM(TOT),0,0, (SUM(FOOD_INOUT+BEVE_INOUT+PRODUCT_INOUT) / SUM(TOT))*100),1) AS INPUT_AVG

, SUM(FOOD_SOMO_INOUT+BEVE_SOMO_INOUT+PRODUCT_SOMO_INOUT) AS SOMO_INOUT

, GROUPING_ID(RVC_GBN,GRP_NM,RVC_CD,DET_NM) AS GP_ID

FROM XX

WHERE RVC_GBN NOT IN ('MC1201','MC1207')

GROUP BY ROLLUP(RVC_GBN,GRP_NM,RVC_CD,DET_NM)

HAVING GROUPING_ID(RVC_GBN,GRP_NM,RVC_CD,DET_NM) NOT IN (1,7)

ORDER BY RVC_GBN,RVC_CD

 

 

 

위 쿼리 실행시 나오는 화면입니다.

MC1202 B/Q 33 Ba 3927279 822196 20.9 0 0 0 0

MC1202 B/Q 34 Wed 19800000 5866298 29.6 0 0 0 0

MC1202 B/Q 계 23727279 6688494 28.2 0 0 0 3

MC1203 F/B 20 In 20388940 11294431 55.4 0 0 0 0

MC1203 F/B 21 On 152551219 252223692 165.3 -12547 0 -16720 0

MC1203 F/B 22 La 26482334 6914122 26.1 0 0 0 0

MC1203 F/B 24 Ra 29878337 9452138 31.6 0 0 0 0

MC1203 F/B 25 Ru 10080516 2871273 28.5 0 0 0 0

MC1203 F/B 26 Lo 13459001 1597204 11.9 0 0 0 0

MC1203 F/B 27 Garden 12377088 2918324 23.6 0 0 0 0

MC1203 F/B 41 Club 280000 131801 47.1 0 0 0 0

MC1203 F/B 101 Bar 5917617 1059982 17.9 0 0 0 0

MC1203 F/B 102 The 69090 812970 1176.7 0 0 0 0

MC1203 F/B 계 271484142 289275937 106.6 -12547 0 -16720 3

소계 295211421 (MC1202+MC1203)

MC1204 L/S 50 Lei 52690963 8809910 16.7 0 0 -1251 0

MC1204 L/S 53 Ten 265445 30048 11.3 0 0 0 0

MC1204 L/S 계 52956408 8839958 16.7 0 0 -1251 3

합계 348167829 304804389 87.5 -12547 0 -17971 15

 

 

문의내용 : 전체 조회 결과에서 MC1202 값에 계와 , MC1203 값에 계를 합해서 MC1203 밑에 소계를 추가하고 싶습니다.

방법이 있을까요 부탁드립니다.

 

이 글에 대한 댓글이 총 2건 있습니다.

SELECT rvc_gbn
     , grp_nm
     , rvc_cd
     , CASE GROUPING_ID(gb, rvc_gbn, rvc_cd)
       WHEN 0 THEN det_nm
       WHEN 1 THEN '계'
       WHEN 3 THEN '소계'
       WHEN 7 THEN '합계'
        END det_nm
     , SUM(tot) tot
     , SUM(m_cost) m_cost
     , ROUND(NVL(SUM(m_cost) / NULLIF(SUM(tot), 0), 0) * 100, 1) m_avg
     , SUM(inout) inout
     , ROUND(NVL(SUM(inout)  / NULLIF(SUM(tot), 0), 0) * 100, 1) input_avg
     , SUM(somo_inout) somo_inout
  FROM (SELECT CASE WHEN rvc_gbn IN ('MC1202','MC1203') THEN 1 ELSE 2 END gb
             , rvc_gbn, grp_nm, rvc_cd, det_nm
             , tot
             , food_cost      +beve_cost      +product_cost       m_cost
             , food_inout     +beve_inout     +product_inout      inout
             , food_somo_inout+beve_somo_inout+product_somo_inout somo_inout
          FROM xx
        )
 GROUP BY ROLLUP(gb, (rvc_gbn, grp_nm), (rvc_cd, det_nm))
 HAVING NOT(GROUPING(rvc_gbn) = 1 AND NVL(gb, 0) = 2)
 ORDER BY gb, rvc_gbn, rvc_cd
;

마농(manon94)님이 2017-11-27 13:27에 작성한 댓글입니다.
이 댓글은 2017-11-27 13:27에 마지막으로 수정되었습니다.

마농님 정말 감사합니다.

 

 

김민수님이 2017-11-28 13:52에 작성한 댓글입니다.
이 댓글은 2017-11-28 13:54에 마지막으로 수정되었습니다. Edit
[Top]
No.
제목
작성자
작성일
조회
41486group by 문법 [1]
찬호
2017-11-28
5589
41485redo log file이 다 차지 않았는데 log switch가 발생하는경우 [1]
goblin
2017-11-22
5417
41484년도별, 달별 해당일수를 구하고 싶은데요... [2]
김경민
2017-11-22
5756
41483소계,합계 문의 드립니다. [2]
김민수
2017-11-20
7291
41481오라클 스케줄링에서 프로세스와 권한에대해 알고싶습니다.. [1]
오린이
2017-11-17
5324
41480오라클 일요일 로우 생성 문의좀 드려요. [1]
염진호
2017-11-15
5358
41479오라클 프로시저 생성 시 오류문제 문의 [1]
손호영
2017-11-10
5495
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.051초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다