SUM函数-NULL问题 [英] SUM function - NULL problem

问题描述

我在使用NULL的sql中的SUM函数方面遇到问题，因此制动了表的所有结构：（x-month，y-cities，value-Nettotal），但是我根据y错误地排列了值-cities，因为没有可用空间，我的意思是0值，下面是使屏幕截图更加清晰的屏幕截图：

I have a problem regarding to a SUM function in sql that gives NULLs, thus brakes all the structure of the table: (x-month,y-cities,value-Nettotal) but i get wrongly arranged values according to a y-cities, because there are no free spaces, i mean 0 values, here is the screenshot to make it more clear:

以及我之前问过但无法得到明确答案的问题的链接：按城市分组

and the link to a question i asked before but couldnt get a clear answer: group by cities

所以我和Dave DuPlantis到目前为止写的是：

So what I and Dave DuPlantis have wrote so far is:

查询：

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

有人能解决这种问题吗？！谢谢大家的帮助！

Does anyone have an idea to solve this kind of problem?! Thank you everyone for help!

推荐答案

正如Leigh所言，每个城市需要相同的月数来填充输出根据需要-通过将发票链接到WHERE子句中的子查询M，您已将其变成内部联接。将查询修改为以下内容应该可以起作用：

As Leigh observes, you need the same number of months for each city for the output to populate as required - by linking invoice to sub-query M in the WHERE clause, you have turned it into an inner join. Amending the query to something like the following should work:

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

这篇关于SUM函数-NULL问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！