Enterprise Manager and Sum up Columns

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

Enterprise Manager and Sum up Columns

Author

Topic

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-13 : 15:28:29

How do I do this Below with Enterprise Manager...If possible

I have a Monthlyexpense column. How do I Sum up this column and put the Total in my ytdexpenses column. I think I figure out the query, but I don't know where to write the query at. Please help.

UPDATE YourTable
SET ytdexpenses =SUM(Monthlyexpense)

will work for Query Analyzer, but do I select a database in there some where? Please help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-13 : 15:34:30

Yes do this in Query Analyer. Yes select a database where you want to run the command.

Tara Kizer

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-13 : 15:36:52

How do I select the database? What do the Query look like?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-13 : 15:37:47

The query is the UPDATE statement. Use the dropdown to switch your database or do this:

USE SomeDatabaseName
GO

UPDATE...

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-13 : 15:41:47

You really need to Learn SQL:
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

CODO ERGO SUM

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-13 : 15:56:51

I think I got an error at the bottom

USE InfoPathBudget
GO
UPDATE TOSS
SET ytdexpenses = SUM(monthlyExpenses)
SET ytdBudgeted = SUM(monthlyBudgeted)
SET ytdCapitalExpenses = SUM(monthlyCapitalExpenses)
SET ytdCapitalBudgeted = SUM(monthlyCapitalBudgeted)

I got this when I ran it
Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-13 : 16:42:25

Sorry, had my update wrong.

UPDATE YourTable
SET YourColumn = (SELECT SUM(YourSumColumn) FROM YourTable)

Tara Kizer

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-13 : 19:29:44

I must need a Stored Procedure for this or a Trigger, because it won't keep on updating once new data is entered into the database.

Please help

Michael

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-14 : 03:57:39

you can create a view to do this instead of having to update the ytd everytime a record is insert or updated.


create view your TOSS_VIEW
as
select <column list ... >
from   TOSS cross join
(
SELECT ytdexpenses        = SUM(monthlyExpenses),
       ytdBudgeted        = SUM(monthlyBudgeted),
       ytdCapitalExpenses = SUM(monthlyCapitalExpenses),
       ytdCapitalBudgeted = SUM(monthlyCapitalBudgeted)
from   TOSS
) s

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources