can do it with SQL without stored procedure .. ? 1

[mnavahan]

HI ALL

i hve one table for ex.
with this field : ID , manufacture , Price
with this data : 1 3 100
2 2 300
2 12 40
2 7 80
2 1 500


can any way without stordedprocedure do below (i need add some of price from first to current :

with this field : ID , manufacture , Price sumofprice
with this data : 1 3 100 100
2 2 300 400
2 12 40 440
2 7 80 520
2 1 500 1020
....


can do without stored procedure ?

 

[swampBoogie]

select id,manufacture,price,
(select sum(price) from t
  where id <= q.id
    and manufacturer < q.manufacturer)
from t as q

I don't see how you can get the data in the you show, unless you have some other column to order by.

 

[mnavahan]

nice but have some problem ...

first my Select is :

select id,manufacture,price from manf;

can give me what t & q ?


and give me with up select ?

THX

 

[swampBoogie]

t represents your table name. q is an arbitray name used for making an outer reference to the table t in the scalar subquery.

 

[mnavahan]

Hi swampboogie

i no understand end comment can give me real example ?

 

[nigelrivett]

How do you decide you want the rows displayed and accumulated in that order?
The way you have described this it is not possible.


select id,manufacturer,price,
(select sum(price) from manf t2
where t2.id < t.id
or (t2.id = t.id and t2.manufacturer <= t.manufacturer))
from manf t
order by id, manufacturer

will give
id manufacturer price
----------- ------------ ----------- -----------
1 3 100 100
2 1 500 600
2 2 300 900
2 7 80 980
2 12 40 1020

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.