Select Statement Ratio Calculation Not Working as Expected

I am trying to create a query that will give me a ratio calculation based on certain counts from two columns which are working however unless there is data in the table, it won’t be able to calculate a ratio.

To mitigate this I want my script to convert all null values to a 1.

This is my current script (I’m removing the rest of the joins because it isn’t necessary to include)

SELECT DISTINCT TB1.Person,
TB2.AllocatedCount as AllocatedCount,
TB3.UnallocatedCount as UnallocatedCount,
AllocatedCount * 1.0 / UnallocatedCount * 1.0 as Ratio,

The above yields the following results which work perfectly provided the table has data for both columns:

I want to change the script so that all null results would represent the number 1 however the ratio doesnt seem to be calculating.

SELECT DISTINCT TB1.Person,
CASE WHEN TB2.AllocatedCount IS NULL THEN 1 ELSE TB2.AllocatedCount as AllocatedCount,
CASE WHEN TB3.UnallocatedCount IS NULL THEN 1 ELSE TB3.UnallocatedCount as UnallocatedCount,
AllocatedCount * 1.0 / UnallocatedCount * 1.0 as Ratio,

Above yields the following results:

Any idea what I’m doing wrong with my script to not calculate the ratio?

>Solution :

You also need to check for NULL values when you calculate the ratio (using ISNULL() here is probably a better option):

SELECT 
   DISTINCT TB1.Person,
   ISNULL(TB2.AllocatedCount, 1) AS AllocatedCount,
   ISNULL(TB3.UnallocatedCount, 1) AS UnallocatedCount,
   ISNULL(TB2.AllocatedCount, 1) * 1.0 / ISNULL(TB3.UnallocatedCount, 1) * 1.0 AS Ratio
FROM 
   ...