Error when calculating a running total (cumulative over the previous periods)

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)

Which would return something like:

YEAR    MONTH     Total Added
-----------------------------
2009    01        25
2009    02        127
2009    03        241

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

I'm trying this:

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)

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.

"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

Are you on 2005 or later, You can break this using a CTE

WITH CTE AS (
SELECT YEAR(MT.Created) as Yr 
    , MONTH(MT.Created) as Mth
    ,( 
    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 
FROM My_Table MT 
WHERE MT.Created > CAST('2009/01/01' AS datetime))

SELECT Yr, Mth, SUM(Total) as Total FROM CTE
GROUP BY Yr, Mth 
ORDER BY Yr, Mth 

You could take the aggregate out of the final query with something like this:

WITH CTE AS 
(SELECT DISTINCT YEAR(MT.Created) AS [Year]
    , MONTH(MT.Created) AS [Month]
FROM My_Table MT
WHERE MT.Created > CAST('2009/01/01' AS datetime)
)
SELECT MT.[Year]
    , MT.[Month]
    ,(
    SELECT COUNT(*) FROM My_Table MT_int
    WHERE MT_int.Created >= CAST('2009/01/01' AS datetime)
        AND (YEAR(MT_int.Created) < MT.[Year]
            OR (YEAR(MT_int.Created) = MT.[Year]
                AND MONTH(MT_int.Created) <= MT.[Month])
            )
        -- the last day of the current month
        -- (Additional conditions can go here)
    ) AS [Total added this month]
FROM CTE MT
ORDER BY MT.[Year], MT.[Month]

I think that should cover all the past orders in a previous year or a previous month in the same year along with all the orders in that month.

Call it the slow way, but you could do it with a function. Don't do it if My_Table is big.

Create Function [dbo].[RunningTotal](@Yr int, @Mnth int)
 Returns int
AS
BEGIN
 Declare @RC int
 Select @RC=count(*)
 From My_Table
 Where Year(Created)<@Yr or (Year(Created)=@Yr and Month(Created) <= @Mnth)

 Return @RC
END