开发者

Generate year to date by month report in SQL [duplicate]

This question already has answers here: Closed 11 years ago.

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 t开发者_运维百科hat can be provided would be greatly appreciated!

Update: The data is stored on an IBM iSeries.


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.


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 


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


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜