Year-To-Date Summary by Month in Access SQL

Question

Given this table, I need to generate a year-to-date summary by month. The result should have one row for each month of the year, and a running total for the year up to that month. I am by no means a novice when it comes to SQL, yet I still have no idea how to achieve this in pure SQL. Does anyone know how to do this? Please note that it needs to be compatible with Microsoft Access SQL.

projectTitle  | completionDate | amount
---------------------------------------
'Project #1'  | 2013-01-12     | 1234
'Project #2'  | 2013-01-25     | 4567
'Project #3'  | 2013-02-08     | 8901
'Project #4'  | 2013-02-15     | 2345
'Project #5'  | 2013-02-20     | 6789
'Project #6'  | 2013-03-01     | 1234
'Project #7'  | 2013-04-12     | 5678
'Project #8'  | 2013-05-06     | 9012
'Project #9'  | 2013-05-20     | 3456
'Project #10' | 2013-06-18     | 7890
'Project #11' | 2013-08-10     | 1234

Example of the expected results

month    | amount
-----------------
'Jan-13' | 5801          -- = Project #1 + Project #2 + Project #3
'Feb-13' | 23836         -- = Project #1 + Project #2 + Project #3 + Project #4 + Project #5
'Mar-13' | 25070         -- ...and so on
'Apr-13' | 30748         
'May-13' | 43216         
'Jun-13' | 51106
'Jul-13' | 51106          
'Aug-13' | 52340

In my example output, you may notice I included July even though the example table did not have any data for July. Ideally, the output would still include July as you see in the example output, however I can live without if necessary.

score 1 · Accepted Answer · answered Jun 10 '13 at 15:13

I found the answer by using a sub-query, and using aliases for the table name.

SELECT FORMAT(t1.completionDate,"mmm-yy") AS [month], (
        SELECT SUM(t2.amount)
        FROM projects AS t2
        WHERE MONTH(t2.completionDate) <= MONTH(t1.completionDate)
    ) AS [amount]
FROM projects AS t1;

score 0 · Answer 2 · edited May 23 '17 at 12:18

0

I think, trying to group it by Month could help. Look here to see how to Group it by Month. Then you should be able to sum the amount an give it out by the Month.

Hope this Idea helps you.

Sarajog

edited May 23 '17 at 12:18

Community

1
1

answered Jun 10 '13 at 13:55

Sarajog

164
16

Sorry, but this doesn't help. I can already group it by month, but the problem is getting each month to include all of the previous months in the total. – Drew Chapin Jun 10 '13 at 13:57
You want a sql query that returns every month to get total of all projects completed at that month or what exactly do you want? – Sarajog Jun 10 '13 at 14:00
I don't think I can explain it any better than I did in the question. But, when the query is run it should have one row for each month up to the current month of the current year. Each row's amount column should be a sum of the all of the projects for the current year up to that rows month. – Drew Chapin Jun 10 '13 at 14:16
I think that will help you: [link](http://stackoverflow.com/questions/4517681/sql-sum-with-condition) – Sarajog Jun 10 '13 at 15:06

score 0 · Answer 3 · answered Jun 10 '13 at 14:21

0

Select Month(CompletionDate) as MthComplete, Year(CompletionDate) as YrComplete, AMT as Sum(Amount)
From MyTable
Group By Year(CompletionDate), Month(CompletionDate)
Order By Year(CompletionDate), Month(CompletionDate)

You'll probably have to putz with another column to get the Year/Month format you're using in your output example, but that will group the data the way you want it.

answered Jun 10 '13 at 14:21

Johnny Bones

8,786
7
52
117

Unfortunately this is not what I'm looking for. Each row of the output should not just be a sum of the that month, but all of the previous months as well. – Drew Chapin Jun 10 '13 at 14:26

Year-To-Date Summary by Month in Access SQL

3 Answers3