table control - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

table control

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2006-12-16 : 00:02:02

hi all,
i wonder if could accomplish this problem in sql..

say i wanna total sum up some qty in table 1, and the sum up qty appear in table 2.. means everytime qty in table 1 change, it will sum up automatically total qty in tbl1 :-

table 1
id qty
1 5
2 5
2 5
1 5

table 2
id SumQty
1 10
2 10

note: table 2 will sum up automatically, isit possible to do this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-16 : 01:39:41

1 You dont need to have seperate table to have sum values
2 It is just group by Issue
Select id,sum(qty) as qty from table group by id
3 Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2006-12-17 : 19:48:17

tq madhivanan,
actually the reason im doing this is because, it's not only one table that change the sum quantity. Imagine more than one table could change this quantity. For eg, table A wants to minus, table B wnats to add... depend on the last update table operation...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-12-17 : 22:09:56

Easiest is to put a trigger on table1 to update the values.

Note - it is often a good idea to maintain aggregate tables like this as aggregates on the transaction table can cause blocking and deadlocks. The next step is to maintain the aggregates on a separate reporting server.

==========================================
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.

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2006-12-18 : 03:47:31

hi nr,

since im quite new to sql, could u pliz elaborate more one ur post.. coz i dont understand a single thing..:P

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-18 : 10:43:55

quote:
Originally posted by maya_zakry

tq madhivanan,
actually the reason im doing this is because, it's not only one table that change the sum quantity. Imagine more than one table could change this quantity. For eg, table A wants to minus, table B wnats to add... depend on the last update table operation...

The select statement I suggested will refelect all updates too

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources