sum COALESCE 0 au lieu de null

Question

Je ne peux pas ajouter des valeurs nulles au lieu de null, voici mon 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

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

le résultat que je veux :

et le résultat que j'obtiens :

Merci à tous pour votre aide !

+ EDIT :

J'ai utilisé la technique de jointure croisée, réécrit le 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

et le résultat est :

Answer 1

Utilisez plutôt CASE

SOMME(CAS OÙ A EST NUL ALORS 0 SINON A FIN)

Answer 2

Vous pouvez le faire dans la base de données comme Lasse l'a suggéré, ou vous pouvez envelopper chaque valeur de sortie dans un fichier Val comme suit :

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

En Val convertira toute valeur non numérique en 0.

Answer 3

Pouvez-vous utiliser ISNULL à la place, par exemple ;

 SUM(ISNULL(AMOUNT,0)) AMOUNT,

?

EDIT : ok, étant donné que le problème semble être des valeurs manquantes plutôt que des nuls en tant que tels. essayez quelque chose comme ceci.

Tout d'abord, créez une table permanente reporting_framework. Celui-ci est basé sur les mois et les années mais vous pouvez l'étendre aux jours si vous le souhaitez.

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

(cela vous donne 6000 rangs, de 2000 à 2499 - ajustez à votre goût !)

Maintenant, nous allons créer une table des pièces et une table des commandes.

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

Maintenant, c'est la table sur laquelle vous basez votre requête, par exemple ;

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

Cet exemple vous aide-t-il ? Il y a probablement beaucoup de meilleures façons de faire (bonjour StackOverflow) mais cela peut vous aider à réfléchir à votre problème. Pas le CROSS JOIN pour obtenir toutes les combinaisons pièces/dates, puis le FULL OUTER JOIN pour y intégrer les commandes. La clause "where" ne fait que contrôler votre plage de dates.