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.

which database you're using? You can use date functions to extract year-month string for mysql for example http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format and then group by this value — varela, Sep 14 '11 at 15:22
@JNK: This is similar to what I am trying to do. The one answer gives me some ideas for this situation. — Swoop, Sep 14 '11 at 15:27

score 3 · Accepted Answer · answered Sep 14 '11 at 15:27

3

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.

answered Sep 14 '11 at 15:27

Icarus

63,293
14
100
115

This is on an IBM iseries using DB2. But your answer definitely gives me some ideas on how I can approach this. – Swoop Sep 14 '11 at 15:31
Good, my solution should work then because DB2 supports CASE statements. – Icarus Sep 14 '11 at 15:32
@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 – navbingo Jan 05 '15 at 14:36

mishau · Answer 2 · 2011-09-15T06:11:02.337

1

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

edited Sep 15 '11 at 06:11

answered Sep 14 '11 at 15:36

mishau

582
4
11

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 Sep 14 '11 at 17:20
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. – mishau Sep 15 '11 at 06:12

score 1 · Answer 3 · answered Sep 14 '11 at 15:54

1

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

answered Sep 14 '11 at 15:54

It looks like the iSeries we are running does not support the SUM function with an OLAP: http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafyolap.htm – Swoop Sep 14 '11 at 17:01
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 Sep 14 '11 at 17:03
@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... – Sep 15 '11 at 07:24
@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 Jan 05 '15 at 14:24

pupssman · Answer 4 · 2011-09-14T20:17:20.087

0

You should try to join the table to itself by month-behind-a-month condition and generate a synthetic month-group code to group by as follows:

 select 
  sum(value),
  year,
  up_to_month
 from (
    select a.value,
           a.year, 
           b.month as up_to_month
     from table as a join table as b on a.year = b.year and b.month => a.month
 ) 
 group by up_to_month, year

gives that:

db2 => select * from my.rep

VALUE       YEAR        MONTH      
----------- ----------- -----------
    100        2011           1
    200        2011           2
    300        2011           3
    400        2011           4

db2 -t -f rep.sql

1           YEAR        UP_TO_MONTH
----------- ----------- -----------
    100        2011           1
    300        2011           2
    600        2011           3
   1000        2011           4

edited Sep 14 '11 at 20:17

answered Sep 14 '11 at 15:34

pupssman

541
2
11

Question: Don't you need to group by `year` and `month` also since you are doing `sum(value)`? – Icarus Sep 14 '11 at 17:10
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. – Icarus Sep 14 '11 at 18:06

Generate year to date by month report in SQL

4 Answers4