sum COALESCE 0 instead of null

Question

i cant add zero values instead of null, here is my 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

and my table:

<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>

the result i want:

and the result i get:

thank you all for the help!

+ EDIT :

I have used the cross join technique, rewrote the 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

and the result is:

Answer 1

Can you use ISNULL instead, ie;

 SUM(ISNULL(AMOUNT,0)) AMOUNT,

?

EDIT: okay, given that the problem seems to be missing values rather than nulls as such. try something like this.

First, create a permanent reporting_framework table. This one is based on months and years but you could extend it into days if you wished.

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;

(this gives you 6000 rows, from 2000 to 2499 - adjust to taste!)

Now we'll make a table of parts and a table of orders

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');

Now this is the table you base your query on, eg;

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

Does this example help? There are probably loads of better ways of doing this (hello StackOverflow) but it might get you started thinking about what your problem is. Not the CROSS JOIN to get all parts/dates combinations and then the FULL OUTER JOIN to get the orders into it. The 'where' clause is just controlling your date range.

Answer 2

You can do it in the database as Lasse suggested, or you can wrap each output value in a Val function, like so:

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

The Val function will convert any non-numeric value to 0.

Answer 3

Use CASE instead

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