Divide by zero error encountered.Warning: Null
value
is
eliminated by an aggregate or other SET operation.
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
);
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
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
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
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
where
(b.DelID
is
null
and
b.Entrydate
BETWEEN
@StartDate
AND
@Enddate
or
b.EntryDate
is
null
)
(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
(ISNULL(Small_Bale_weight,
0
))/((
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
group
by
cte.secnam,cte.Smallbale_QTY,cte.Bigbale_Weight,cte.Small_Bale_weight,cte.Bigbale_QTY,emp.Employee_QTY)
select
*
from
cte1
where
Smallbale_QTY+Small_Bale_weight+Bigbale_QTY+Bigbale_Weight+Total_QTY+Total_Weight+Employee_QTY+Percentage<>0
What I have tried:
i tried to used null but it is say isnull require to argument which i tried to adjust Like isnull(A,0) but not getting adjusted..
(
Select
(ISNULL(Small_Bale_weight,
0
))/(ISNULL(
SELECT
SUM(val)
FROM
(
VALUES
(Bigbale_Weight),
(Small_Bale_weight )
)
AS
value(val)
))*100.
0
)
as
Percentage,
If you want to avoid a division by zero error when using ISNULL then don't choose zero as the substitution for null in the divisor - use 1 instead (as division by 1 is the original value).
However, all of your
val
values must be null for
SUM(val)
to return null. We know that isn't the case so that isn't actually your underlying problem.
Look at
Bigbale_Weight + Small_Bale_weight for secnam = M-Baby, M-HHR, m-HR etc … all zero. You are calculating percentage for
all
rows in the table
Change the calculation for percentage to handle that fact e.g.
CASE
WHEN
ISNULL(Bigbale_Weight,
0
) + ISNULL(Small_Bale_weight,
0
) <>
0
THEN
(ISNULL(Small_Bale_weight,
0
) / ISNULL(Bigbale_Weight + Small_Bale_weight,
1
)) *
100
.
0
ELSE
0
END
AS
[Percentage],Note the square brackets around "Percentage" - that's because it is a reserved word in SQL and shouldn't really be used as a column name (unless surrounded by [ ] ).
I've also simplified the calculation by removing SUM here but your use of the sub-query is arguably better as you wouldn't need the ISNULL I've used. However, I suspect my version is faster.
Finally, I'm upvoting your question because you gave us everything we needed to reproduce the problem - thank you!
BTW: [w] and [s] have been added by me to correct spellings.
Now, take a look at your code:
(
Select
(ISNULL(Small_Bale_weight,
0
))/(
ISNULL(
SELECT
SUM(val)
FROM
(
VALUES
(Bigbale_Weight),
(Small_Bale_weight )
)
AS
value(val)
))*100.
0
)
as
Percentage, ...
Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad
spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or
edit the question
and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it.
Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
