sql - TSQL - Percentage calculation not working

Question

I have created subqueries which return a count e.g. below;

I am trying to code them as LH / (GN + MarketRent + Commercial) * 100 but where there is a value to calculate the query returns a null.

subquery which joins the main query, using left outer.

SELECT        BLOCKS.[BLOCK-REF] AS [BLOCK-SUBB-REF], count(BLOCKS.[PLACE-REF]) NoOfMR
FROM            [SQLViewsPro2EOD].[dbo].[IH-LOCATION] AS BLOCKS INNER JOIN
                         [SQLViewsPro2EOD].[dbo].[IH-LOCATION] AS SUBB ON BLOCKS.[BLOCK-REF] = SUBB.[PLACE-REF] 
                         AND SUBB.[LOCATION-TYPE] IN ('BLOCK', 'SUBBLOCK', 'CBLOCK', 'HOSTEL', 'SCHEME') --and  SUBB.[BLOCK-REF] <> ''
                         and BLOCKS.[LOCATION-TYPE] in ('FLAT', 'BEDSIT', 'BUNG', 'DUPLEX', 'ENDTER', 'GFLAT', 'GFLATOG', 'HOSTEL', 'HOUSE', 'MAISON', 'SEMDET', 'ROOM'
                         ,'BLOCK', 'SUBBLOCK', 'CBLOCK', 'HOSTEL', 'SCHEME')
                        right JOIN  [DBAdmin].[dbo].[BusinessPlanTenureMap] bp on bp.[ESTATE-CODE] = BLOCKS.[ESTATE-CODE]
WHERE 
bp.[Business Plan Tenure] = 'MARKET RENTAL' and
(BLOCKS.[LOCATION-STS] <> 'D')
group by BLOCKS.[BLOCK-REF]

This the the code I have produced so far taking into account the divide by zero error in the main query. The % Leaseholders column shows null values rather than the % calculation.

,cast(nullif(isnull(lhso.[NoOfLH-SO],0),0) as int)/ cast(nullif(isnull(gen.NoOfGN,0),0) + nullif(isnull(lhso.[NoOfLH-SO],0),0) + nullif(isnull(cm.NoOfCM,0),0) as int) * 100 '% leaseholder'

GN  LH/SO   MARKET RENTAL   COMMERCIAL  % Leaseholders
0   0   0   0   0.00
4   2   0   0   14.29
2   2   0   0   16.67
0   0   0   0   0.00
3   1   0   0   12.50
1   3   0   0   75.00

The table is the expected output

Any tips advice appreciated. Thanks

Thanks Jeremy, This part of the query works, it totals.

cast(isnull((nullif((isnull(gen.NoOfGN,0) 
+ isnull(lhso.[NoOfLH-SO],0) 
+isnull(mr.NoOfMR,0)
+isnull(rl.NoOfRL,0) 
+isnull(cm.NoOfCM,0) 
+isnull(hs.NoOfHS,0)
+isnull(op.NoOfOP,0)
+isnull(sh.NoOfSH,0) 
+isnull(ec.NoOfEC,0) ),0)),0)as int) Total

but when I add this

isnull(nullif(lhso.[NoOfLH-SO],0),0) / 

to the front part it returns an error Divide by zero error encountered.

I think I catered for the null values...

question from:https://stackoverflow.com/questions/66048637/tsql-percentage-calculation-not-working

Answer 1

Waitting for answers