mssql 하위쿼리에 sum이있는데 이걸 다시 sum으로 묶을순없을까요?ㅠㅠ 0 16 2,535

by 지메이비 mssql [2022.05.11 16:40:40]


(case when a.isBuyingVat=''Y'' then
			dbo.UF_MCVT2(round(SUM(a.buyingAmt)/(case when SUM(a.materialQty-a.rtnQty)=0 then 1 else SUM(a.materialQty-a.rtnQty) end),0),0,0) 
		else 
			dbo.UF_MCVT2(round(SUM(a.buyingAmt+a.buyingVat)/(case when SUM(a.materialQty-a.rtnQty) = 0 then 1 else SUM(a.materialQty-a.rtnQty) end),0),0,0) 
		end ) as price,

 

이런식으로 isbuying이 Y라면 위에 함수

다른것이면 밑에 함수를 타는데 

MCVT란 함수가 소수점제거하는 함수거든요.. 그럼 계산식은 round(SUM(a.buyingAmt)/(case when SUM(a.materialQty-a.rtnQty)=0 then 1 else SUM(a.materialQty-a.rtnQty) end),0),0,0) 이건데

저 쿼리 전체를 SUM으로 묶으면 집계 또는 하위 쿼리가 포함된 식에서는 집계 함수를 수행할 수 없습니다. 라고 에러가뜨네요 ㅠㅠ 어떤방법이 있을까요??

by 마농 [2022.05.11 16:59:50]

이미 SUM 을 한걸 다시 또 이중으로 SUM 할수는 없습니다.
뭔가 잘못 생각하고, 접근방식을 잘못 잡으신 듯 합니다.


by 지메이비 [2022.05.11 17:01:16]

맨앞 case 부터 sum함수로 잡을순없는건가요??


by 마농 [2022.05.11 17:06:23]

지금 원하시는 부분은 논리적으로 맞지 않습니다.
정확하게 무엇을 원하는지 다시 한번 고민해 보셔야 합니다.


by 지메이비 [2022.05.11 17:15:42]

roll up 함수로 총계를 구했는데 roll up함수에도 저 계산식이 타서 그렇습니다..

총계를 구했지만 저 함수때문에 총계가 이상하게 나오는 상황입니다


by 마농 [2022.05.11 17:14:57]
-- SUM 을 다시 또 SUM 하려면 인라인뷰 이용하세요.
SELECT SUM(price) price
  FROM (SELECT ROUND
               ( CASE WHEN a.isBuyingVat=''Y'' THEN SUM(a.buyingAmt) ELSE SUM(a.buyingAmt+a.buyingVat) END
               / CASE WHEN SUM(a.materialQty - a.rtnQty) = 0 THEN 1 ELSE SUM(a.materialQty - a.rtnQty) END
               , 0) price
          FROM t a
         GROUP BY a.isBuyingVat
        ) a
;

 


by 마농 [2022.05.11 17:18:13]

사견입니다만.
나누기 연산의 모수가 0 이면 오류가 발생하여 위와 같이 1로 대체한 듯 한데요.
이렇게 되면 그 의미가 이상해집니다. 정확한 값이 아닌 이도 저도 아닌 값이 되어 버립니다.
모수가 0 일 때는 결과 자체가 안나오게 하는게 차라리 더 맞다고 할 수 있습니다.


by 마농 [2022.05.11 17:22:24]

전반적으로 문제가 있는 쿼리인 듯 합니다.
나누기의 모수 0 을 1 로 대체하는게 과연 맞는지도 의문입니다.
전체 쿼리와 원하는 의도를 설명해 주세요.
샘플 자료가 있으면 더 좋습니다.(원본 대비 결과표)


by 지메이비 [2022.05.11 17:29:09]

                개수           가격      합계금액    공급가액       세액

                1.0          22230        24453       22230          2223
                               1.0          42450        46695       42450         4245
총계                         10831.4      2872         31104682    30329937    774745

일부분만 가져왔습니다.

이런식인데 공급가액 / 개수 해서 가격을 나타내고있습니다.. 합계금액은 공급가액+세액이구요

총계가 22230+42450 의 결과가 나와야하는데 

저 계산식을 같이타서 33104682 / 10831.4 = 2872 라는 총계가 나와버렸어요 ㅠㅠ

 

select 
		ISNULL(DBO.UF_LARGENAME(b.dealercode,b.largeCode), ''총 계'') as largeName,
		b.materialCode,
		isNull(b.cmsMaterialCode,'''') as cmsMaterialCode,
		b.materialName,
		b.dimension ,
		DBO.UF_UNITNAME(b.unitCode) as unitName ,
		SUM(a.materialQty-a.rtnQty) as qty,

		(case when a.isBuyingVat=''Y'' then
			dbo.UF_MCVT2(round(SUM(a.buyingAmt)/(case when SUM(a.materialQty-a.rtnQty)=0 then 1 else SUM(a.materialQty-a.rtnQty) end),0),0,0) 
		else 
			dbo.UF_MCVT2(round(SUM(a.buyingAmt+a.buyingVat)/(case when SUM(a.materialQty-a.rtnQty) = 0 then 1 else SUM(a.materialQty-a.rtnQty) end),0),0,0) 
		end ) as price,
		SUM(a.buyingAmt + a.buyingVat) as buyingTotal,
		SUM(a.buyingAmt) as buyingAmt,
		SUM(a.buyingVat) as buyingVat,
		a.isBuyingVat,
		b.largeCode
	from orders a,material b



' ' ' ' 


group by rollup((b.dealerCode,b.largeCode,b.materialCode,b.materialName,b.dimension,b.unitCode,b.cmsMaterialCode,a.isbuyingVat)) 

 


by 마농 [2022.05.11 17:48:34]
-- 쿼리를 간결하게 바꾸고
-- SUM 의 위치를 CASE 밖으로 꺼내 봤습니다.
-- 애초에 sum 의 위치에 문제가 있는 것 같네요.
SELECT ...
       ROUND(
       SUM( CASE WHEN a.isBuyingVat = ''Y'' THEN a.buyingAmt ELSE a.buyingAmt + a.buyingVat END
          / CASE WHEN a.materialQty - a.rtnQty = 0 THEN 1 ELSE a.materialQty - a.rtnQty END
          ), 0) AS price,
       ...
  FROM orders a,material b
...
 GROUP BY ROLLUP((b.dealerCode, b.largeCode, b.materialCode, b.materialName, b.dimension, b.unitCode, b.cmsMaterialCode, a.isbuyingVat))
;

 


by 지메이비 [2022.05.11 17:59:53]

답변감사합니다 ㅠㅠ

하지만 

갯수가 1개짜리는 잘나오는데

2.0    14700   16170    14700    1470

2개부터는 14700/2 = 7350 으로나와야하는데

그대로 1개갯수로 나오네요 ㅠㅠ 


by 마농 [2022.05.11 18:21:55]
-- 2단계 Group By --
SELECT ISNULL(dbo.uf_largename(dealercode, largeCode), ''총 계'') AS largeName
     , materialCode
     , isNull(cmsMaterialCode, '''') AS cmsMaterialCode
     , materialName
     , dimension
     , dbo.uf_unitname(unitCode) AS unitName
     , SUM(materialQty - rtnQty) AS qty
     , SUM(price) AS price
     , SUM(buyingAmt + buyingVat) AS buyingTotal
     , SUM(buyingAmt) AS buyingAmt
     , SUM(buyingVat) AS buyingVat
     , isBuyingVat
     , largeCode
  FROM (SELECT b.dealercode
             , b.largeCode
             , b.materialCode
             , b.cmsMaterialCode
             , b.materialName
             , b.dimension
             , b.unitCode
             , SUM(a.materialQty - a.rtnQty) AS qty
             , ROUND
               ( CASE WHEN a.isBuyingVat = ''Y'' THEN SUM(a.buyingAmt) ELSE SUM(a.buyingAmt + a.buyingVat) END
               / CASE WHEN SUM(a.materialQty - a.rtnQty) = 0 THEN 1 ELSE SUM(a.materialQty - a.rtnQty) END
               , 0) price
             , SUM(a.buyingAmt + a.buyingVat) AS buyingTotal
             , SUM(a.buyingAmt) AS buyingAmt
             , SUM(a.buyingVat) AS buyingVat
             , a.isBuyingVat
          FROM orders   a
             , material b
         WHERE ...
           AND ...
         GROUP BY b.dealerCode, b.largeCode, b.materialCode, b.materialName, b.dimension, b.unitCode, b.cmsMaterialCode, a.isbuyingVat
        ) c
 GROUP BY ROLLUP((dealerCode, largeCode, materialCode, materialName, dimension, unitCode, cmsMaterialCode, isbuyingVat))
;

 


by 마농 [2022.05.12 08:03:52]

가격의 의미가 공급가 / 개수 의 의미라면?
합계에서도 똑같이 적용되어야 하는게 맞을 듯 합니다.
지금 생각하고 계신 원하는 결과가 틀린 것일 수 도 있습니다.

그리고.
(합계금액 / 합계수량) 으로 계산하는게 맞는지도 의문입니다.
(개별금액 / 개별수량) 의 값을 AVG(평균)해야 하는게 아닐지? 생각해 봅니다.


by 지메이비 [2022.05.12 14:32:43]

답변감사합니다..

혹시 group by rollup 함수를 사용할 때 

(case when a.isBuyingVat=''Y'' then
            dbo.UF_MCVT2(round(SUM(a.buyingAmt)/(case when SUM(a.materialQty-a.rtnQty)=0 then 1 else SUM(a.materialQty-a.rtnQty) end),0),0,0) 
        else
            dbo.UF_MCVT2(round(SUM(a.buyingAmt+a.buyingVat)/(case when SUM(a.materialQty-a.rtnQty) = 0 then 1 else SUM(a.materialQty-a.rtnQty) end),0),0,0) 
        end ) as price,

이부분을 제외하고 총계내는방법이 있을까요?? SUM이 들어가있어서 그런지 의도치않게 총계가 들어가버리는데 제외하는방법이있나요?


by 마농 [2022.05.12 14:47:01]

-- DECODE(GROUPING(dealerCode), 0, (해당부분)) AS price -- Oracle
CASE GROUPING(dealerCode) WHEN 0 THEN (해당부분) END AS price


by 지메이비 [2022.05.12 14:53:02]

DECODE는 mssql에서도쓸수있나요??


by 지메이비 [2022.05.12 15:05:25]

감사합니다 !!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입