Update, Set, Select Sum - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Update, Set, Select Sum

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2003-05-09 : 08:19:27

I am having trouble with the following statement. I want to total the amount collected and update the temporary table by Id. When I run this I get the grand total on every record.
Thanks for you help. vmon

UPDATE ##AgencyPerf
SET ##AgencyPerf.PreviousCollected =
(SELECT SUM(dbo.tblCollections_Trans.Amount_Adjusted)
FROM ##AgencyPerf LEFT OUTER JOIN
dbo.tblCollections_Trans ON ##AgencyPerf.ID_Eclipse = dbo.tblCollections_Trans.ID_Eclipse
WHERE dbo.tblCollections_Trans.Date_Transact < (CONVERTDATETIME, '2002-05-05 00:00:00', 102)
and dbo.tblCollections_Trans.Id_Eclipse = ##AgencyPerf.Id_Eclipse
GROUP BY ##AgencyPerf.ID_Eclipse)
WHERE dbo.tblCollections_Trans.Id_Eclipse = ##AgencyPerf.Id_Eclipse

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-09 : 09:28:21

Test the calculation of the totals you need..

SELECT AP.ID_Eclipse AS ID,
SUM(CT.Amount_Adjusted) AS Total

FROM ##AgencyPerf AP

INNER JOIN dbo.tblCollections_Trans CT ON AP.ID_Eclipse = CT.ID_Eclipse

WHERE CT.Date_Transact < '2002-05-05'

GROUP BY AP.ID_Eclipse

Now this query ought to work and it can be tested in QA. I've used an INNER JOIN (to get just the needed rows) and Aliased the table names for ease of reading, and the datetime conversion in WHERE is implicit, so it's easier to read.

Now how do we construct an update that will take the totals from the above query an store them in AgencyPerf??

UPDATE A

SET A.PreviousCollected = Total

FROM ##AgencyPerf A
INNER JOIN (
-- INSERT THE ABOVE QUERY HERE
) B ON A.ID_Eclipse = B.ID

I haven't tried this, but it should be close with minor debugging.

Sam

vmon
Yak Posting Veteran

63 Posts

Posted - 2003-05-09 : 10:40:57

Sam,

Thank you for the help. I am very new at this and have learned alot from what you have aready showed me. Question. Where you aliased the tables names I would/could use the actual names? I am getting an error and have narrowed it to the Update statement where you had ) B ON A.ID_Eclipse = B.ID

I tested the select and it works.

The error is: Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '.'.

This is what I have right now.

UPDATE AP
SET AP.PreviousCollected = Total
FROM AP INNER JOIN

(SELECT AP.ID_Eclipse As Id, SUM(dbo.CT.Amount_Collected) AS Total
FROM AP
INNER JOIN dbo.CT ON AP.ID_Eclipse = dbo.CT.ID_Eclipse
WHERE dbo.CT.Date_Transact < '2020-05-05'
GROUP BY AP.ID_Eclipse)

dbo.CT ON AP.Id_Eclipse = dbo.CT.Id_Eclipse

Thanks again,
vmon

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-09 : 10:55:46

Don't do dbo.CT . CT is an owner-qualified tablename on the INNER JOIN.

Next, you have selected an outer alias 'AP' on the UPDATE that is identical to the alias 'AP' used in the SELECT. This will cause unresolvable ambiguity. I had chosen 'A' and 'B' for the UPDATE JOIN to avoid this problem.

Cut and paste this and let me know if it works:

UPDATE A

SET A.PreviousCollected = Total

FROM ##AgencyPerf A
INNER JOIN (
SELECT AP.ID_Eclipse AS ID, SUM(CT.Amount_Adjusted) AS Total

FROM ##AgencyPerf AP

INNER JOIN dbo.tblCollections_Trans CT ON AP.ID_Eclipse = CT.ID_Eclipse

WHERE CT.Date_Transact < '2002-05-05'

GROUP BY AP.ID_Eclipse
) B ON A.ID_Eclipse = B.ID

Edited by - SamC on 05/09/2003 10:57:03

Edited by - SamC on 05/09/2003 10:57:29

Edited by - SamC on 05/09/2003 12:20:36

vmon
Yak Posting Veteran

63 Posts

Posted - 2003-05-09 : 11:39:59

Sam,

Works great! Thank you! I still don't understand the alias. Would there be a way to do this without using an alias?

Thanks again,
vmon

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-09 : 12:13:57

Why don't you want to use an alias? Make life a lot easier.

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-09 : 12:19:40

You can always substitute the tablename for the alias, but to my knowledge, there is no performance penalty using an alias, and it is far more readable.

Sam

vmon
Yak Posting Veteran

63 Posts

Posted - 2003-05-09 : 15:24:04

I am very new to T-SQL. I have a only worked with MSAccess up to now. No SQL Server. Not that I don't want to Alias' it's I don't fully understand their use. I think I tried using the table names and I got an error on the update after the select ).

Thanks again for the help.
Still Learning
vmon

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-09 : 15:36:57

Access even can use aliases...look at properties of a table in the QBE grid and you'll see it there.

Also look in BOL (Books Online) for Alias

An alias is simply a reference to something, in this case a table.

So you can do:


   SELECT  a.col1, b.col2
     FROM tblSomeVeryLongTableNameThatWouldBeStupidToRetype_1 a
LEFT JOIN tblSomeVeryLongTableNameThatWouldBeStupidToRetype_2 b
       ON a.col2 = b.col2

Get the picture?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources