데이터베이스 질문과 답변
안녕하세요?
원하는 결과는 Market code별(중분류) 합계를 원하지만 하기 쿼리 보시면
Dimension value code 별(소분류)로 밖에 합계 산출이 안됩니다.
도움 부탁드려요.
SELECT
CASE
WHEN (DIM_IN.[Dimension Value Code] LIKE 'A%') THEN 'Alternative Energy'
WHEN (DIM_IN.[Dimension Value Code] LIKE 'E%') THEN 'Others - Non-Core'
WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%') THEN
CASE WHEN (DIM_IN.[Dimension Value Code] LIKE 'FM%') THEN 'Magna Fluids'
WHEN (DIM_IN.[Dimension Value Code] LIKE 'F%') THEN 'Industrial MRO - Fluids'
END
WHEN (DIM_IN.[Dimension Value Code] LIKE 'H%') THEN 'Heavy Industries'
WHEN (DIM_IN.[Dimension Value Code] LIKE 'I%') THEN 'Infrastructure'
WHEN (DIM_IN.[Dimension Value Code] LIKE 'M%') THEN 'Marine'
WHEN (DIM_IN.[Dimension Value Code] LIKE 'O%') THEN 'OEM (Electronics/Appliances)'
WHEN (DIM_IN.[Dimension Value Code] LIKE 'P%') THEN 'Industrial MRO - Polymers'
WHEN (DIM_IN.[Dimension Value Code] LIKE 'T%') THEN 'Transportation'
END AS MARKET_CODE,
DIM_IN.[Dimension Value Code],
SUM(SL.[QTY_ SHIPPED (BASE)]) as [Actual Shipped]
FROM
SALES HEADER AS SH
INNER JOIN SALES LINE] AS SL ON SH.NO_ = SL.[DOCUMENT NO_]
LEFT OUTER JOIN DOCUMENT DIMENSION] AS DIM_IN ON DIM_IN.[DOCUMENT NO_] = SL.[DOCUMENT NO_] AND DIM_IN.[LINE NO_] = SL.[LINE NO_] AND
DIM_IN.[DIMENSION CODE] = 'INDUSTRY'
group by DIM_IN.[Dimension Value Code]
by 마농
[2018.10.17 11:37:46]
1. 그룹핑 기준이 틀렸네요.
- group by (소분류) 하셨네요.
- group by (중분류) 하셔야죠.
2. 그런데 중분류가 case 문이네요.
- case 구문 전체를 group by 에 그대로 넣으시던가?
- 인라인뷰로 감싸고 밖에서 알리아스로 그룹바이 하시면 됩니다.
-- 인라인뷰로 감싸고 알리아스로 그룹바이 --
SELECT MARKET_CODE
, SUM([QTY_ SHIPPED (BASE)]) AS [Actual Shipped]
FROM (SELECT CASE
CASE WHEN SUBSTRING(DIM_IN.[Dimension Value Code], 1, 2) = 'FM' THEN 'FM'
ELSE SUBSTRING(DIM_IN.[Dimension Value Code], 1, 1)
END
WHEN 'A' THEN 'Alternative Energy'
WHEN 'E' THEN 'Others - Non-Core'
WHEN 'FM' THEN 'Magna Fluids'
WHEN 'F' THEN 'Industrial MRO - Fluids'
WHEN 'H' THEN 'Heavy Industries'
WHEN 'I' THEN 'Infrastructure'
WHEN 'M' THEN 'Marine'
WHEN 'O' THEN 'OEM (Electronics/Appliances)'
WHEN 'P' THEN 'Industrial MRO - Polymers'
WHEN 'T' THEN 'Transportation'
END AS MARKET_CODE
, SL.[QTY_ SHIPPED (BASE)]
FROM SALES HEADER AS SH
INNER JOIN SALES LINE] AS SL
ON SH.NO_ = SL.[DOCUMENT NO_]
LEFT OUTER JOIN DOCUMENT DIMENSION] AS DIM_IN
ON DIM_IN.[DOCUMENT NO_] = SL.[DOCUMENT NO_]
AND DIM_IN.[LINE NO_] = SL.[LINE NO_]
AND DIM_IN.[DIMENSION CODE] = 'INDUSTRY'
) a
GROUP BY MARKET_CODE
;
by tae
[2018.10.17 13:27:56]
빠른 답변 감사합니다!
많은 도움 되었습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.