问题描述
我有一个表,我们称它为 My_Table
,其中有一个 Created
datetime列(在SQL Server中),我正在尝试获取一份报告,该报告历史上显示了在特定时间内每月在 My_Table
中有多少行。现在我知道我可以显示每个月有 个添加了:
I have a table, let's call it My_Table
that has a Created
datetime column (in SQL Server) that I'm trying to pull a report that shows historically how many rows were to My_Table
by month over a particular time. Now I know that I can show how many were added each month with:
SELECT YEAR(MT.Created), MONTH(MT.Created), COUNT(*) AS [Total Added]
FROM My_Table MT
GROUP BY YEAR(MT.Created), MONTH(MT.Created)
ORDER BY YEAR(MT.Created), MONTH(MT.Created)
返回以下内容:
YEAR MONTH Total Added
-----------------------------
2009 01 25
2009 02 127
2009 03 241
但是,我想获取给定时间段内 total 的列表大小(称其为您想要的;运行总计,累计总和,历史报告):
However, I want to get the total list size over the given period (call it what you will; a running total, a cumulative sum, a historical report):
YEAR MONTH Total Size
-----------------------------
-- 2008 12 325
2009 01 350
2009 02 477
2009 03 718
我正在尝试:
SELECT YEAR(MT.Created)
, MONTH(MT.Created)
,(
SELECT COUNT(*) FROM My_Table MT_int
WHERE MT_int.Created BETWEEN
CAST('2009/01/01' AS datetime)
AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MT.Created)+1,0))
-- the last day of the current month
-- (Additional conditions can go here)
) AS [Total added this month]
FROM My_Table MT
WHERE MT.Created > CAST('2009/01/01' AS datetime)
GROUP BY YEAR(MT.Created), MONTH(MT.Created)
ORDER BY YEAR(MT.Created), MONTH(MT.Created)
但是,SQL Server响应此错误:
However, SQL Server is responding with this error:
Msg 8120, Level 16, State 1, Line 1
Column 'My_Table .Created' is invalid in the select list because
it is not contained in either an aggregate function or the GROUP BY clause.
我只是知道,但我遗漏了一些明显的东西,但在走开之后回来盯着它看了一会儿,我很茫然。因此,如果有人愿意指出地球上的内容,而我在这里不见了(或指出一种更好的方法),我将永远感激不已。
I just know I'm missing something obvious, but after walking away and coming back and staring at it for a while I'm at a loss. So if someone would be so kind as to point out what on earth I'm missing here (or point me at a better way of doing it) I'd be eternally grateful.
推荐答案
正在运行表示一行一行。因此,一种方法是将前几个月的总和添加到当前月份。要处理年份界限,您还需要每个组的最小/最大日期。交叉应用有点RBAR,但是可以清楚地知道(发生了什么情况。)。
"Running" implies row by row. So one way is to sum previous months and add it to current month. To deal with year boundaries, you also take min/max date per group. The CROSS APPLY is slightly RBAR but makes it clear(er?) what is happening.
;WITH cTE AS
(
SELECT
MIN(Created) AS FirstPerGroup,
MAX(Created) AS LastPerGroup,
YEAR(MT.Created) AS yr, MONTH(MT.Created) AS mth, COUNT(*) AS [Monthly Total Added]
FROM MY_Table MT
GROUP BY YEAR(MT.Created), MONTH(MT.Created)
)
SELECT
C1.yr, c1.mth, SUM(C1.[Monthly Total Added]),
ISNULL(PreviousTotal, 0) + SUM(C1.[Monthly Total Added]) AS RunningTotal
FROM
cTE c1
CROSS APPLY
(SELECT SUM([Monthly Total Added]) AS PreviousTotal FROM cTE c2 WHERE c2.LastPerGroup < C1.FirstPerGroup) foo
GROUP BY
C1.yr, c1.mth, PreviousTotal
ORDER BY
C1.yr, c1.mth
这篇关于计算运行总计时的错误(前几个期间的累计)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!