sum COALESCE 0 instead of null
Solution 1
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.
Solution 2
Use CASE instead
SUM(CASE WHEN A IS NULL THEN 0 ELSE A END)
Solution 3
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.
user775917
Updated on June 15, 2022Comments
-
user775917 almost 2 years
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: