I am facing this error when i am adding Percentage column (
Msg 8134, Level 16, State 1, Line 38
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.
( Select (Small_Bale_weight)/((SELECT SUM(val)
FROM (VALUES (Bigbale_Weight),
(Small_Bale_weight )
)AS value(val)
))*100.0) as Percentage,
here is my whole data and query
CREATE TABLE #Category (CID INT,CName VARCHAR(50));
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50));
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,CID INT,weight int);
CREATE TABLE #Bigbalprd(BID INT,CodeItem INT,SecID INT,CID INT,Bpqty INT,Entrydate DATETIME , DelID int,Bweight int);
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,weight int,Entrydate DATETIME,DelID int);
CREATE TABLE #Emp_Strength(EID INT,SecID INT,QTY int, Entrydate DATETIME);
GO
INSERT INTO #Category VALUES(1,'INDIA'),(2,'INDIA(Cut)'),(3,'Pakistan'),(4,'Pakistan(Cut)')
INSERT INTO #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');;
INSERT INTO #ItemMasterFile VALUES(1,'A',1,1,100)
, (2,'B',2,2,100)
, (3,'C',3,3,100)
, (4,'D',4,null,100)
, (5,'e',5,null,100)
, (6,'f',6,null,100)
, (7,'g',4,2,100)
, (8,'h',4,3,100)
, (9,'K',2,2,100)
, (10,'L',4,3,100)
, (11,'M',2,4,100);
INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,'01-06-2019',null,100)
, (2,3,3,3,1,'02-06-2019',null,100)
, (3,4,null,4,1,'03-06-2019',null,100)
, (4,4,null,4,1,'04-06-2019',null,100)
, (4,5,null,4,1,'04-06-2019',null,100);
INSERT INTO #Probale VALUES(1,1,1,100,'01-06-2019',null)
, (2,3,1,200,'02-06-2019',null)
, (3,11,1,200,'03-06-2019',null)
, (4,10,1,200,'08-06-2019',null)
, (3,8,1,200,'03-06-2019',null)
, (4,9,1,200,'08-06-2019',null)
, (4,9,1,200,'08-06-2019',null);
INSERT INTO #Emp_Strength VALUES(1,1,4,'01-05-2019')
, (2,3,5,'02-05-2019')
, (3,3,3,'03-05-2019')
, (4,4,7,'04-05-2019');
DECLARE @StartDate DATETIME, @Enddate DATETIME
SET @StartDate = '01-06-2019'
SET @Enddate = '09-06-2019'
;WITH emp
as
(
select Secnam,ISNULL(sum(e.qty),0) Employee_QTY from #Sections s
left join #Emp_Strength e on s.secid=e.secid
where (Entrydate BETWEEN @StartDate AND @Enddate or Entrydate is null)
group by Secnam
),cte
AS
(
SELECT DISTINCT Sec.Secnam,
ISNULL(SUM(b1.Bpqty),0)Bigbale_QTY,ISNULL(sum(b1.Bweight),0)Bigbale_Weight,
ISNULL(SUM(b.prdQTY),0)Smallbale_QTY,ISNULL(SUM(case when b.prdQTY is null then 0 else i.weight end ),0)Small_Bale_weight
--ISNULL(SUM(emp.QTY),0)Employee_QTY
FROM #ItemMasterFile i
LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem
LEFT OUTER JOIN #Bigbalprd b1 ON i.CodeItem = b1.CodeItem
Full Outer Join #Sections sec on i.SecID=sec.SecID
--left join Emp_Strength emp on emp.SecID = sec.SecID
--FULL OUTER JOIN Sections s ON i.SecID = s.SecID
where (b.DelID is null and b.Entrydate BETWEEN @StartDate AND @Enddate or b.EntryDate is null )
and
(b1.DelID is null and b1.EntryDate BETWEEN @StartDate AND @Enddate or b1.EntryDate is Null )
GROUP BY sec.Secnam
),cte1 as (
SELECT cte.secnam, Smallbale_QTY,Small_Bale_weight, Bigbale_QTY, Bigbale_Weight,
( SELECT SUM(val)
FROM (VALUES (Bigbale_QTY)
, (Smallbale_QTY)
) AS value(val)
) AS Total_QTY,
( SELECT SUM(val)
FROM (VALUES (Bigbale_Weight),
(Small_Bale_weight )
)AS value(val)
) as Total_Weight,
( Select (Small_Bale_weight)/((SELECT SUM(val)
FROM (VALUES (Bigbale_Weight),
(Small_Bale_weight )
)AS value(val)
))*100.0) as Percentage,
coalesce(Employee_QTY,0) Employee_QTY
FROM cte left join emp on cte.secnam=emp.secnam)
select * from cte1
where Smallbale_QTY+Small_Bale_weight+Bigbale_QTY+Bigbale_Weight+Total_QTY+Total_Weight+Employee_QTY+Percentage<>0
Please Guide
