SUM函数 - NULL问题(SUM function - NULL problem)

我有一个关于在SQL的SUM函数，提供NULL值，因而刹车表的所有结构的问题：根据Y型城市（X-月，Y型城市，价值Nettotal），但我得到错误地安排值，因为没有自由空间，我的意思是0值，这里是截图更清楚：

并链接到一个问题，我问过，但不可能得到一个明确的答案：按城市

所以我和戴夫DuPlantis已经写了这么远是：

查询：

<cfquery name="GET_SALES_TOTAL" datasource="#dsn#">
    SELECT SUM(COALESCE(NETTOTAL,0)) NETTOTAL,
           SC.CITY_ID,
           SC.CITY_NAME,
           M.INVOICE_MONTH
    FROM SETUP_CITY SC 
        LEFT OUTER JOIN COMPANY C 
            ON SC.CITY_ID = C.CITY 
                LEFT OUTER JOIN #DSN2_ALIAS#.INVOICE I 
                    ON C.COMPANY_ID = I.COMPANY_ID
        , 
        (
            SELECT DISTINCT DATEPART(MM,INVOICE_DATE) INVOICE_MONTH 
            FROM #DSN2_ALIAS#.INVOICE
        ) M
    WHERE PURCHASE_SALES = 1 
    AND DATEPART(MM,I.INVOICE_DATE) = M.INVOICE_MONTH 
    AND SC.COUNTRY_ID=1
    GROUP BY M.INVOICE_MONTH,
             SC.CITY_ID,
             SC.CITY_NAME
    ORDER BY M.INVOICE_MONTH,
             SC.CITY_ID,
             SC.CITY_NAME
</cfquery>

和表：

<table cellpadding="3" cellspacing="1" class="color-border">
    <tr class="color-header">
        <td class="txtbold" nowrap width="100">Aylar / Sehirler</td>
        <cfoutput query="GET_SALES_GRAND_TOTAL" group="city_id">
            <td class="txtbold">#city_name#</td>
        </cfoutput>
    </tr>
    <cfoutput query="GET_SALES_TOTAL" group="invoice_month"><!--- months first --->
        <tr class="color-row"><!--- month-specific stuff goes here --->
            <td class="txtbold">
                #invoice_month#                         
            </td>
            <cfoutput group="city_id"><!--- city-specific stuff --->
                <td>
                    #tlformat(nettotal,2)#<!--- format NETTOTAL however you want here --->
                </td>
            </cfoutput>
        </tr>
    </cfoutput>
</table>

有没有人有一个想法，要解决这样的问题？谢谢大家的帮助！

作为利观察，你需要同样长的时间为每个城市的输出根据需要填充 - 通过在WHERE子句中联发票子查询男，你已经把它变成一个内部联接。修改查询像下面应该工作：

<cfquery name="GET_SALES_TOTAL" datasource="#dsn#">
SELECT SUM(COALESCE(NETTOTAL,0)) NETTOTAL,
       SC.CITY_ID,
       SC.CITY_NAME,
       M.INVOICE_MONTH
FROM SETUP_CITY SC 
    LEFT OUTER JOIN COMPANY C 
        ON SC.CITY_ID = C.CITY 
    CROSS JOIN  
    (   SELECT DISTINCT DATEPART(MM,INVOICE_DATE) INVOICE_MONTH 
        FROM #DSN2_ALIAS#.INVOICE
    ) M
            LEFT OUTER JOIN #DSN2_ALIAS#.INVOICE I 
                ON C.COMPANY_ID = I.COMPANY_ID
               AND DATEPART(MM,I.INVOICE_DATE) = M.INVOICE_MONTH 
WHERE PURCHASE_SALES = 1 
AND SC.COUNTRY_ID=1
GROUP BY M.INVOICE_MONTH,
         SC.CITY_ID,
         SC.CITY_NAME
ORDER BY M.INVOICE_MONTH,
         SC.CITY_ID,
         SC.CITY_NAME
</cfquery>