如果存在空金额，如何将总和显示为“o” [英] How to display sum as 'o' if there are null amounts

问题描述

我使用以下查询将金额显示为'0'

I am using the below query to display the amount as '0'

SELECT isnull(SUM(amount),0) as amount from getbillsfortaxinvoice WHERE code IN ('TT') and fno = '02112017416202742' and bill_desc not like '%GST%' GROUP BY folio_Id,room_no,guest_name

i使用isnull将NULL值替换为'0'。但结果是空的，而且总和后不是'0'



我试过的：



有人可以帮我这个



i也试过用



COALESCE（）功能



但结果为空。

i have replaced the NULL values with '0' as using isnull. But the result is empty and not '0' after the sum

What I have tried:

can someone help me with this

i have also tried with

COALESCE() FUNCTION

but the amount is resulting as empty.

推荐答案

MSDN写道：

SUM只能与数字列一起使用。 忽略空值。

SUM can be used with numeric columns only. Null values are ignored.

所以，替换：

So, replace:

isnull(SUM(amount),0)

with：

with:

SUM(ISNULL(amount,1))

注意：零的总和会让你......零！



详情请见：

SUM（Transact-SQL）| Microsoft Docs [ ^ ]

ISNULL（Transact-SQL）| Microsoft Docs [ ^ ]

试一试

Try it

SELECT ISNULL(SUM(AMOUNT),0) FROM(SELECT isnull(SUM(amount),0) as amount from getbillsfortaxinvoice WHERE code IN ('TT') and fno = '02112017416202742' and bill_desc not like '%GST%' GROUP BY folio_Id,room_no,guest_name) as X

这篇关于如果存在空金额，如何将总和显示为“o”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！