table count - 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 count

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-23 : 01:36:40

hi all,
how do we sum up all the quantity in table A, and save the summary quantity into table B? can we do it live? i mean evtime quantities in table A change, the total sum in table B also change accordingly.. pliz help... thanks in advance

~~~Focus on problem, not solution~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-23 : 01:50:25

2 option that i can think of :
1. use trigger on table A to update the total sum in table B.
2. Create a view out of table A & B with total sum as one of the column.

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-02-23 : 02:07:09

hi khtan

1.what method can we use to trigger table A? isit function or what?
2.using view? total sum column in each column, in view.. how do we use view in SP? can we call view in SP? i rarely use view in my codebehind, just use it for analyzing only..
im interested in point 1. maybe somebody could spoonfeed me again with some function :P

~~~Focus on problem, not solution~~~

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-23 : 02:26:43

populate the table by
insert qtytbl (item, qty)
select item, sum(qty)
from tableA
group by item

then

create trigger x on tableA for insert, update, delete
as
update qtytbl
set qty = qty - a.qty
from qtytbl
join (select item, qty = sum(qty) from deleted a group by item) a
on a.item = qtytbl.itemupdate qtytbl

set qty = qty + a.qty
from qtytbl
join (select item, qty = sum(qty) from inserted a group by item) a
on a.item = qtytbl.item

insert qtytbl
(item, qty)
select a.item, a.qty
from
(select item, qty = sum(qty) from inserted qroup by item) a
left join qtyrbl
on qtytbl.item = a.item
where qtytbl.item is null
go

It would be better to use the trigger to insert into a transaction table with just the inserted and deleted rows and use a scheduled task to update the qtytbl if there is a lot of activity.

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-23 : 02:31:14

"1.what method can we use to trigger table A? isit function or what?"
I mean the table trigger.
Take a look at
http://www.sqlteam.com/item.asp?ItemID=3850
http://www.sqlteam.com/item.asp?ItemID=6494

"2.using view? total sum column in each column, in view.. how do we use view in SP? can we call view in SP? i rarely use view in my codebehind, just use it for analyzing only.."
View is like a virtual table. It is basically a stored SELECT query

"im interested in point 1. maybe somebody could spoonfeed me again with some function"
Option 2 might be a better choice

.

As usual, we can't help you much without knowing your table structure, sample data and the result that you want. And don't forget a brief description on what you are trying to achieve ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources