sum COALESCE 0 而不是 null

Question

我无法添加零值而不是 null，这是我的 sql:

 SELECT
                        S.STOCK_ID,
                        S.PRODUCT_NAME,
                        SUM(COALESCE(AMOUNT,0)) AMOUNT,
                        DATEPART(MM,INVOICE_DATE) AY
                    FROM
                        #DSN3_ALIAS#.STOCKS S
                            LEFT OUTER JOIN DAILY_PRODUCT_SALES DPS ON S.STOCK_ID = DPS.PRODUCT_ID
                    WHERE
                        MONTH(INVOICE_DATE) >= #attributes.startdate# AND 
                        MONTH(INVOICE_DATE) < #attributes.finishdate+1#
                    GROUP BY
                        DATEPART(MM,INVOICE_DATE),
                        S.STOCK_ID,
                        S.PRODUCT_NAME
                    ORDER BY
                        S.PRODUCT_NAME

和我的表：

<cfoutput query="get_sales_total" group="stock_id">
                            <tr height="20" class="color-row">
                                <td>#product_name#</td>
                                <cfoutput group="ay"><td><cfif len(amount)>#amount#<cfelse>0</cfif></td></cfoutput>
                            </tr>
                        </cfoutput>

我想要的结果：

我得到的结果：

谢谢大家的帮助！

+编辑：

我使用了交叉连接技术，重写了sql：

SELECT
                        SUM(COALESCE(AMOUNT,0)) AMOUNT,S.STOCK_ID,S.PRODUCT_NAME,DPS.AY
                    FROM
                        #DSN3_ALIAS#.STOCKS S
                            CROSS JOIN (SELECT DISTINCT <cfif attributes.time_type eq 2>DATEPART(MM,INVOICE_DATE) AY<cfelse>DATEPART(DD,INVOICE_DATE) AY</cfif> 
                            FROM DAILY_PRODUCT_SALES) DPS
                            LEFT OUTER JOIN DAILY_PRODUCT_SALES DP ON S.STOCK_ID = DP.PRODUCT_ID AND 
                            <cfif attributes.time_type eq 2>DATEPART(MM,DP.INVOICE_DATE)<cfelse>DATEPART(DD,DP.INVOICE_DATE)</cfif> = DPS.AY
                    WHERE
                    <cfif attributes.time_type eq 2>
                        MONTH(INVOICE_DATE) >= #attributes.startdate# AND 
                        MONTH(INVOICE_DATE) < #attributes.finishdate+1#
                    <cfelse>
                        MONTH(INVOICE_DATE) = #attributes.startdate#
                    </cfif>
                    <cfif len(trim(attributes.product_cat)) and len(attributes.product_code)>
                        AND S.STOCK_CODE LIKE '#attributes.product_code#%'
                    </cfif>
                    GROUP BY DPS.AY,S.STOCK_ID,S.PRODUCT_NAME
                    ORDER BY DPS.AY,S.STOCK_ID,S.PRODUCT_NAME

结果是：

Answer 1

使用 CASE 代替

SUM(CASE WHEN A IS NULL THEN 0 ELSE A END)

Answer 2

您可以按照 Lasse 建议在数据库中执行此操作，也可以将每个输出值包装在 Val 函数中，如下所示：

<cfoutput group="ay"><td>#Val(amount)#</td></cfoutput>

Val 函数将转换任何非数字值至 0。

Answer 3

你能用 ISNULL 代替吗，即；

 SUM(ISNULL(AMOUNT,0)) AMOUNT,

？

编辑：好的，考虑到问题似乎是缺少值而不是空值。尝试这样的事情。

首先，创建一个永久的 reporting_framework 表。这是基于月份和年份的，但如果您愿意，您可以将其延长为几天。

<代码>

create table reporting_framework
([month] smallint, [year] smallint);
go

declare @year smallint;
declare @month smallint;

set @year=2000;
while @year<2500 
begin
  set @month=1;
  while @month<13
  begin
    insert into reporting_framework ([month], [year]) values (@month, @year);
    set @month=@month+1;
  end
  set @year=@year+1;
end

select * from reporting_framework;

<代码>
（这为您提供了 6000 行，从 2000 到 2499 - 根据口味进行调整！）

现在我们将制作一个零件表和一个订单表

create table parts
([part_num] integer, [description] varchar(100));
go

insert into parts (part_num, [description]) values (100, 'Widget');
insert into parts (part_num, [description]) values (101, 'Sprocket');
insert into parts (part_num, [description]) values (102, 'Gizmo');
insert into parts (part_num, [description]) values (103, 'Foobar');

create table orders
([id] integer, part_num integer, cost numeric(10,2), orderdate datetime);
go

insert into orders ([id], part_num, cost, orderdate) values
(1, 100, 49.99, '2011-10-30');
insert into orders ([id], part_num, cost, orderdate) values
(2, 101, 109.99, '2011-10-31');
insert into orders ([id], part_num, cost, orderdate) values
(3, 100, 47.99, '2011-10-31');
insert into orders ([id], part_num, cost, orderdate) values
(4, 102, 429.99, '2011-11-01');
insert into orders ([id], part_num, cost, orderdate) values
(5, 101, 111.17, '2011-11-01');
insert into orders ([id], part_num, cost, orderdate) values
(6, 101, 111.17, '2011-11-01');
insert into orders ([id], part_num, cost, orderdate) values
(7, 103, 21.00, '2011-09-15');

现在这是您查询的基础表，例如；

select rf.month, rf.year, p.description, sum(isnull(o.cost,0))
from reporting_framework rf cross join parts p
full outer join orders o 
on rf.year=year(o.orderdate) and rf.month=month(o.orderdate)
and p.part_num=o.part_num
where rf.year='2011'
group by p.description, rf.month, rf.year
order by rf.year, rf.month, p.description

这个例子有帮助吗？可能有很多更好的方法可以做到这一点（你好 StackOverflow），但它可能会让你开始思考你的问题是什么。
不是获取所有零件/日期组合的交叉连接，然后是获取订单的完整外部连接。
“where”子句只是控制您的日期范围。