(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으로 묶으면 집계 또는 하위 쿼리가 포함된 식에서는 집계 함수를 수행할 수 없습니다. 라고 에러가뜨네요 ㅠㅠ 어떤방법이 있을까요??
-- 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 ;
개수 가격 합계금액 공급가액 세액
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))
-- 쿼리를 간결하게 바꾸고 -- 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)) ;
-- 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)) ;
답변감사합니다..
혹시 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이 들어가있어서 그런지 의도치않게 총계가 들어가버리는데 제외하는방법이있나요?