Aggregating on a Condition

[aravindk]

Hello all,

I am trying to summarize my data based on a column value. e.g:

My data structure is as follows:

RptMonth Sales
200201 500
200201 200
200202 100
200203 200
200203 500
200301 100
200302 100
200302 50
200306 200

I am trying to write an sql statement which will return one record per month that will include total sales UPTO that month. The result should look like

200201 700
200202 800
200203 1500
200304 1500
200305 1500
.
.
.
200301 1600
200302 1750
200303 1750
200304 1750
200305 1750
200306 1950

Currently I am using a union query which works fine for me, but it requires monthly maitenance. Is there a easier way to achieve the desired results? My database is on SQL Server 2000.

Any help is greatly appreciated!

 

[JamesLean]

SELECT DISTINCT rptmonth,
  (SELECT SUM(sales) FROM t WHERE rptmonth <= t1.rptmonth)
FROM t t1

--James

 

[aravindk]

James,

This works like charm! Thanks!

../Aravind.

 

[aravindk]

James,

I am trying to add another column in this equation and the problem is the column is in a diff table. If I have to join table p in this select statement where the common field is inv_no, this is what I came up with:

SELECT DISTINCT t1.rptmonth, t2.custtype
(SELECT SUM(sales) FROM t WHERE rptmonth <= t1.rptmonth)
FROM t t1, p t2 where t1.inv_no = t2.inv_no

How do i modify the subselect so that it returns the sum(sales) for that month AND that custtype only.

I hope it is clear enough. If not, pl. ask.

Thanks again,