May 3, 2006 at 10:01 am
Good afternoon. I've got a view of data in the format below.
Name M1Prod M2Prod...
I understand that it would probably be best to do a running total on the underlying data table, but is it possible to have the following output?
Name M1Prod CumSumAfterM1 M2Prod CumSumAfterM2...
The CumSumAfterM1 would essentially be the same value as M1Prod, but CumSumAfterM2 would be CumSumAfterM1 + M2Prod and CumSumAfterM3 would be CumSumAfterM2 + M3Prod.
I know that I can use the line below.
select name, m1prod, m1prod as cumsum.., m2prod, cumsum...+ m2prod as cumsum2... from tablename
However, is there some slicker way to do this? Thanks.
Chris
May 3, 2006 at 10:14 am
No nothing really any slicker.
May 3, 2006 at 3:22 pm
You cannot use aliased columns defined in a select later on in the same select statement
This fails: select name, m1prod as cumsum1, cumsum1 + m2prod as cumsum2 from test
You may want to use a view and try something like this:
create table test (name varchar(50) Primary Key, M1Prod int, M2Prod int, M3Prod int, M4Prod int)
insert test values('Jeff', 1, 1, 1, 1)
insert test values('Chris', 2, 1, 2, 1)
go
create view runningtotals
as
select name, m1prod CumM1, m1prod + m2prod CumM2, m1prod + m2prod + m3prod CumM3, m1prod + m2prod + m3prod + m4prod CumM4
from test
go
select t.name, m1prod, CumM1, m2prod, CumM2, m3prod, CumM3, m4prod, CumM4
from test t
inner join runningtotals rt
on t.name = rt.name
go
drop view runningtotals
drop table test
go
May 3, 2006 at 3:34 pm
Firstly, thanks, Jeff. I understand that the code that I posted is not correct. I only posted the code to convey the general meaning of what I was trying to accomplish. So, for anyone else, please don't debug the code that I've posted, but do let me know if you have any other interesting ideas. Thanks.
Chris
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply