data calculation

Question

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

Answer 1

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

Answer 2

Another alternative is to use NULLIF instead of case.

   Instead of

,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).

Answer 3

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