在累积和SQL上返回NULL

I have a table of individual sales, for which I would like to summarise into two columns, with a monthly total in one and a cumulative sum in another.

我有一份个人销售表,我想将其总结为两列,每月总计为一列,累计金额为另一列。

select Year, Month, 'Acquisition Income' = SUM(PAYMENTAMOUNT), 
        'Acquisition Cumulative' = (select SUM(PAYMENTAMOUNT)
                                        from #CampaignPayments b
                                        where (a.Year >= b.Year and a.Month >= b.Month)
                                        or (a.Year > b.Year) ) 
into #CampaignTotals
from #CampaignPayments a
group by YEAR, MONTH
order by YEAR asc, MONTH asc

However, I am running into some trouble, where some months, there were no payments, thus my 'Acquisition Income' for that month is NULL.

但是,我遇到了一些麻烦,几个月来没有付款,因此那个月我的“收入收入”是NULL。

Year    Month   Acquisition Income  Acquisition Cumulative
2013    5       121782               121782
2013    6       25959.2              147741.2
2013    7       2875                 150616.2
2013    8       1995                 152611.2
2013    9       625                  153236.2
2013    10      590                  153826.2
2013    11      935                  154761.2
2013    12      550                  155311.2
2014    1       777                  156088.2
2014    2       210                  156298.2
2014    3       75                   156373.2
2014    4       520                  156893.2
2014    5       150                  157043.2
2014    6       340                  157383.2
2014    7       NULL                 NULL
2014    8       30                   157413.2

How do I

我如何能

(a): Return 0 rather than NULL in the 'Acquisition Income' column ( I have tried coalesce(SUM(paymentamount),0) as well as isnull, but neither work.)

(a):在“收购收入”栏中返回0而不是NULL(我尝试过合并(SUM(paymentamount),0)以及isnull,但都没有工作。)

(b): Continue to calculate the 'Acquisition Cumulative' column, even when no payments were made in that month? (i.e. the sum is the same as the previous month)

(b):继续计算“收购累积”栏,即使该月没有付款? (即总和与上个月相同)

To be honest, I am not totally understanding how the cumulative sum works - to me it seems like as each row is returned, it should sum all payments that meet the criteria in the where/or clause, so I am not sure why having no payments in that month would affect the sum for the previous months?

说实话,我并不完全理解累积金额是如何工作的 - 对我来说似乎每行返回,它应该总结满足where / or子句中的条件的所有付款,所以我不确定为什么没有那个月的付款会影响前几个月的金额吗?

Thanks so much in advance.

非常感谢提前。

1 个解决方案

#1

Here you go

SELECT  [Year], 
       [Month],  
       [Acquisition Income], 
       SUM([Acquisition Income]) OVER( ORDER BY [Year], [Month]
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS [Acquisition Cumulative]
INTO #CampaignTotals
FROM
    (

    SELECT Year, Month, ISNULL(SUM(PAYMENTAMOUNT), 0) AS 'Acquisition Income'          
    FROM #CampaignPayments a
    GROUP BY YEAR, MONTH    
    ) AS OuterSet