sql - TSQL - 百分比计算不起作用

Question

我创建了返回计数的子查询，例如下面；

我正在尝试将它们编码为LH / (GN + MarketRent + Commercial) * 100但有一个值来计算查询返回一个空值。

使用左外部连接主查询的子查询。

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]

这是我到目前为止生成的代码，考虑到主查询中的除以零错误。% Leaseholders 列显示空值而不是 % 计算。

,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

该表是预期的输出

任何提示建议表示赞赏。谢谢

谢谢杰里米，这部分查询有效，总计。

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

但是当我添加这个

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

到前面部分它返回一个错误除以零错误遇到。

我想我迎合了空值......

标签： sqlsql-servertsql