Обсуждение: variables in SQL??
anyone know if SQL has variables? what im trying to do is have a Sum of a colum.. as it goes forwards with the cursor.. like so: Price|Sum 5|5 4|9 10|19 2|21 7|28 i can do it in the accessing language.. like PHP, Python, Perl etc.. but i wanted to know if the actuall DB could do it? any ideas?
> what im trying to do is have a Sum of a colum.. as it goes forwards with the > cursor.. > like so: > > Price|Sum > 5|5 > 4|9 > 10|19 > 2|21 > 7|28 I think what you mean is called running sum, I had the same problem before, and I found no other solution than creating a column for it, and calculating its values by a function. Yours, V.Paul
> what im trying to do is have a Sum of a colum.. as it goes forwards I don't think this is what you want, but I suppose it might help.... Table = simple +----------------------------------+----------------------------------+----- --+ | Field | Type | Length| +----------------------------------+----------------------------------+----- --+ | price | int4 | 4 | +----------------------------------+----------------------------------+----- --+ => select price,count(price) from simple group by price; price|count -----+----- 1| 4 2| 7 4| 8 5| 5 7| 16 9| 1 10| 12 (7 rows) => create view simple_v as select price,count(price) from simple group by price; CREATE 19503 1 => select * from simple_v; price|count -----+----- 1| 4 2| 7 4| 8 5| 5 7| 16 9| 1 10| 12 (7 rows) => insert into simple (price) values (5); INSERT 19504 1 => select * from simple_v; price|count -----+----- 1| 4 2| 7 4| 8 5| 6 7| 16 9| 1 10| 12 (7 rows)
You can create a running total provided that you have a unique sequentially increasing (or decreasing) ID for each row. See the following example: create table tran(id int primary key, price dec(8,2)); insert into tran values(1,5.00); insert into tran values(2,4.00); insert into tran values(3,10.00); insert into tran values(4,2.00); insert into tran values(5,7.00); select price, (select sum(price) from tran as d1 where d1.id <= d2.id) as "sum" from tran as d2; price | sum -------+------- 5.00 | 5.00 4.00 | 9.00 10.00 | 19.00 2.00 | 21.00 7.00 | 28.00 (5 rows) Francisco wrote: > > what im trying to do is have a Sum of a colum.. as it goes forwards with the > > cursor.. > > like so: > > > > Price|Sum > > 5|5 > > 4|9 > > 10|19 > > 2|21 > > 7|28 > ==================================== Craig Johannsen Critical Path Consulting, Inc. 604-762-1514 http://members.home.net/cjohan/cpath ====================================