Generate year to date by month report in SQL

Question

Possible Duplicate:
Running total by grouped records in table

I am trying to put together an SQL statement that returns the SUM of a value by month, but on a year to date basis. In other words, for the month of March, I am looking to get the sum of a value for the months of January, February, and March.

I can easily do a group by to get a total for each month by itself, and potentially calculate the year to date value I need in my application from this data by looping through the results set. However, I was hoping to have some of this work handled with my SQL statement.

Has anyone ever tackled this type of problem with an SQL statement, and if so, what is the trick that I am missing?

My current sql statement for monthly data is similar to the following:

Select month, year, sum(value) from mytable group by month, year

If I include a where clause on the month, and only group by the year, I can get the result for a single month that I am looking for:

select year, sum(value) from mytable where month <= selectedMonth group by year

However, this requires me to have a particular month pre-selected or to utilize 12 different SQL statements to generate one clean result set.

Any guidance that can be provided would be greatly appreciated!

Update: The data is stored on an IBM iSeries.

Solution 1

declare @Q as table 
(
mmonth INT,
value int 
)

insert into @Q
values
(1,10),
(1,12),
(2,45),
(3,23)

select sum(January) as UpToJanuary, 
sum(February)as UpToFebruary,
sum(March) as UpToMarch from (
select 
case when mmonth<=1 then sum(value) end as [January] ,
case when mmonth<=2 then sum(value) end as [February],
case when mmonth<=3 then sum(value) end as [March]
from @Q
group by mmonth
) t

Produces:

UpToJanuary UpToFebruary    UpToMarch
22          67              90

You get the idea, right?

NOTE: This could be done easier with PIVOT tables but I don't know if you are using SQL Server or not.

Solution 2

create table mon
(
[y] int not null,
[m] int not null,
[value] int not null,
primary key (y,m))

select a.y, a.m, a.value, sum(b.value) 
from mon a, mon b 

where a.y = b.y and a.m >= b.m
group by a.y, a.m, a.value 

---

2011    1   120 120
2011    2   130 250
2011    3   500 750
2011    4   10  760
2011    5   140 900
2011    6   100 1000
2011    7   110 1110
2011    8   90  1200
2011    9   70  1270
2011    10  150 1420
2011    11  170 1590
2011    12  600 2190

Solution 3

As far as I know DB2 does support windowing functions although I don't know if this is also supported on the iSeries version.

If windowing functions are supported (I believe IBM calls them OLAP functions) then the following should return what you want (provided I understood your question correctly)

select month, 
       year, 
       value,
       sum(value) over (partition by year order by month asc) as sum_to_date
from mytable 
order by year, month 

Comments

[varela]

which database you're using? You can use date functions to extract year-month string for mysql for example dev.mysql.com/doc/refman/5.1/en/… and then group by this value

[Swoop]

@JNK: This is similar to what I am trying to do. The one answer gives me some ideas for this situation.

[Swoop]

This is on an IBM iseries using DB2. But your answer definitely gives me some ideas on how I can approach this.

[Icarus]

Good, my solution should work then because DB2 supports CASE statements.

[Swoop]

It looks like the iSeries we are running does not support the SUM function with an OLAP: publib.boulder.ibm.com/infocenter/iseries/v5r4/…

[Icarus]

I don't think your query will work; the fact that you are including value as a column will make the row repeat as many times are there are rows for the same month. Further, I tested it with SQL Server (although I had to change the syntax a bit to make it work) and the sum_to_date column is returning the total for that month only, not the cumulative value UP TO that month for the same year.

[Icarus]

Question: Don't you need to group by year and month also since you are doing sum(value)?

[Icarus]

Ahhh this one is SO CLOSE and so elegant that I almost want to upvote it except for the fact that, it repeats the value column as many times as there are rows for a particular month and that it doesn't take into account different years (i.e. the where clause should say where a.m>=b.m AND a.y=b.y).

[Icarus]

Still doesn't work. You are aliasing sum(value) as year (i.e. you are missing a comma after sum(value)). Also, your logic is flawed because you are joining on a.year=b.year and b.month>a.month so you are excluding rows that don't match the condition; for example, if you have rows for month 1,2,3,4 for year 2011, only months 2 and 3 will be returned. Months 1 and 4 will be excluded.

[mishau]

yes I threw an idea I used 2011 year limitation. The idea is not how to group the data, but how to limit summarization up to current month. For all the pedants' sake I've corrected the script.

[a_horse_with_no_name]

@Icarus: for Oracle and PostgreSQL the sum() will return the sum up to the "current row" (which is how the windowing functions are defined - apparently SQL Server takes a different shot at that). I know that it will repeat every value, but I'm not sure what kind of output Swoop actually wants...

[navbingo]

@a_horse_with_no_name: Hi,i am having a table with the same structure as above (i.e) month int , year int , cp float and comp float.. i have only these 4 columns.. i need to generate a report based on these values like Quaterwise where q1- jan -mar, half yearly h1 jan-june ; in sql server 2008.. please help me how to achieve it.. Thanx in advance

[navbingo]

@Icarus: Hi,i am having a table with the same structure as above (i.e) month int , year int , cp float and comp float.. i have only these 4 columns.. i need to generate a report based on these values like Quaterwise where q1- jan -mar, half yearly h1 jan-june ; in sql server 2008.. please help me how to achieve it.. Thanx in advance