why the strange results?

[jayvaughn]

can someone explain why D, is returning 0??? and what a proper solution would be?
ultimately i'm just trying to calculate the percent of increase between A and B


SELECT distinct a.diobli as library

,sum(a.diobsz) as a

,(select sum(diobsz) from dskspace.QAEZ170714
where diobli = a.diobli) as b

,sum(a.diobsz) -
(select sum(diobsz) from dskspace.QAEZ170714
where diobli = a.diobli) as c

,(sum(a.diobsz) -
(select sum(diobsz) from dskspace.QAEZ170714
where diobli = a.diobli)) / sum(a.diobsz) as d

from qusrsys.qaezdisk a
where a.diobli = 'LDATAT3X'
group by a.diobli

LDATAT3X

31210811392

30677762048

533049344

0

[Jim_IT]

Field diobsz must be an big integer. It is doing integer math and since the value is less than 1 it is 0.

Select
a,b,a-b as c,(a-b)/a as d
From
(values
(Cast(31210811392 As BigInt),
 Cast(30677762048 As BigInt))) As A (A,B)

31210811392	30677762048	533049344	0

Convert the expression to a decimal data type first then do arithmetic operation.

Select
a,b,a-b as c,decimal((a-b),19,0)/a as d
From
(values
(Cast(31210811392 As BigInt),
 Cast(30677762048 As BigInt))) As A (A,B)

31210811392	30677762048	533049344	0.017078996675

Jim

[jayvaughn]

that makes sense - i rarely use BIGINT, therefore the confusion... thanks for educating me.