CODEWITHSUNDEEP
sqlms-access
user3735997
user3735997

Reputation: 23

How to SUM() for past 3 months for every field in a column

I have a table in MS Access that looks something like this

Date                    Item                        Count
-----------------------------------------------------------
1/1/2000                ABC                         5
1/1/2000                DEF                         8
1/1/2000                GHI                         3
1/2/2000                JKL                         1
1/2/2000                ABC                         7
1/3/2000                GHI                         8
1/3/2000                ABC                         4

And I want to count for every item in every month, what's the sum of the past 3/6/12 months and it should look something like this

    Date                    Item                        Count (3M)
--------------------------------------------------------------------
    1/1/2000                ABC                         5
    1/1/2000                DEF                         8
    1/1/2000                GHI                         3
    1/2/2000                JKL                         1
    1/2/2000                ABC                         12 (5+7)
    1/3/2000                GHI                         11 (3+8)
    1/3/2000                ABC                         16 (5+7+4)

Is there anyway this can be done in SQL queries?

Upvotes: 2

Views: 3471

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

Try a self-join on Item and t2.Date <= t1.Date and t2.Date >= dateadd("m",-3,t1.Date).

SELECT
    t1.Date,
    t1.Item,
    SUM(t2.Count) three_sum
FROM
    Table1 t1
    JOIN Table1 t2 
        ON t1.Item = t2.Item
        AND t2.Date <= t1.Date
        AND t2.Date >= dateadd("m",-3,t1.Date)
GROUP BY t1.Date, t1.Item
ORDER BY t1.Date ASC

Upvotes: 2

Related Questions