data calculation

# data calculation

• Thursday, May 10, 2012 6:33 PM

How to do calculation for the below data if I have a 0 or null? the calculation is BBB/AAA and like to see o if there is a 0, see calResults below. Thanks.

City   state  total    AAA   BBB           calResults

cityA, VA,   178,    122,    56             0.45

cityB, CA,   123,     34,     89             2.61

cityB, CA,   123,     34,      89            2.61

cityB, CA,   123,     0,        89            0

cityB, CA,   123,     54,       0             0

### All Replies

• Thursday, May 10, 2012 6:49 PM

```Declare @tvTable Table (
City		varchar(10)
,StateId	varchar(2)
,Total		int
,AAA		int null
,BBB		int
)

Insert @tvTable
Select 'cityA', 'VA',   178,    122,    56
UNION ALL
Select 'cityB', 'CA',   123,     34,       89
UNION ALL
Select 'cityB', 'CA',   123,     34,       89
UNION ALL
Select 'cityB', 'CA',   123,     0,        89
UNION ALL
Select 'cityB', 'CA',   123,     54,       0
UNION ALL
Select 'cityB', 'CA',   123,     null,       0

Select	City
,StateId
,Total
,AAA
,BBB
,Case
When AAA = 0 Then 0
When AAA Is Null Then 0
Else Cast(BBB As real) / Cast(AAA As real)
End calResults
From	@tvTable```

• Thursday, May 10, 2012 6:53 PM
Moderator

Another alternative is to use NULLIF instead of case.

,Case
When AAA = 0 Then 0
When AAA Is Null Then 0
Else Cast(BBB As real) / Cast(AAA As real)
End calResults

Use:

cast(coalesce(cast(bbb as real) / nullif(aaa, 0), 0) as real)

Something else to keep in mind: the "Real" datatype is different than the "Float" datatype (in case that matters).

• Thursday, May 10, 2012 6:57 PM

Interesting.  I didn't even know "NULLIF" was a thing.

```Declare @tvTable Table (
City		varchar(10)
,StateId	varchar(2)
,Total		int
,AAA		int null
,BBB		int
)

Insert @tvTable
Select 'cityA', 'VA',   178,    122,    56
UNION ALL
Select 'cityB', 'CA',   123,     34,       89
UNION ALL
Select 'cityB', 'CA',   123,     34,       89
UNION ALL
Select 'cityB', 'CA',   123,     0,        89
UNION ALL
Select 'cityB', 'CA',   123,     54,       0
UNION ALL
Select 'cityB', 'CA',   123,     null,       0

Select	City
,StateId
,Total
,AAA
,BBB
,Case
When AAA = 0 Then 0
When AAA Is Null Then 0
Else Cast(BBB As real) / Cast(AAA As real)
End calResults
,IsNull(Cast(BBB As real) / NULLIF(AAA, 0), 0)
From	@tvTable```

http://msdn.microsoft.com/en-us/library/ms177562.aspx