我有一张个人销售额的表,我想把它总结成两列,一列是每月总额,另一列是累计总额。
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然而,我遇到了一些麻烦,有几个月,没有付款,因此我那个月的‘收购收入’是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我该怎么做
(a):在“收购收入”一栏中返回0而不是NULL (我尝试过coalesce(SUM(paymentamount),0)和isnull,但都不起作用)。
(b):即使当月没有付款,也要继续计算“购置累计”栏?(即总和与上月相同)
老实说,我并不完全理解累计金额是如何工作的-在我看来,当每行返回时,它应该对满足where/or子句中条件的所有付款求和,所以我不确定为什么在该月没有付款会影响前几个月的金额?
提前谢谢你了。
发布于 2015-05-11 16:51:10
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 OuterSethttps://stackoverflow.com/questions/30162611
复制相似问题