SUM function - NULL problem

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

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

the query:

<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 PURC开发者_如何学GoHASE_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>

and table:

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

---

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>