Totalling by ID's - 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 2008 Forums

Transact-SQL (2008)

Totalling by ID's

Author

Topic

akira220984
Starting Member

13 Posts

Posted - 2010-02-08 : 07:13:25

I have the following code:

declare @orderTotal money
set @orderTotal = 0

update #order
set ordertotal = @orderTotal,
@orderTotal = @orderTotal + orderamount
where #order.personid = #person.id

select * from #order

While it makes sense to me, I am getting the following error:

Msg 4104, Level 16, State 1, Line 65
The multi-part identifier "#person.id" could not be bound.

Any ideas?

Thanks :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 07:19:54

update should be


update o
set o.ordertotal = @orderTotal, 
@orderTotal = @orderTotal + o.orderamount
from #order o
join #person p
where o.personid = p.id

akira220984
Starting Member

13 Posts

Posted - 2010-02-08 : 07:24:29

Thanks Visakh,

Now the error has changed to Incorrect syntax near the keyword 'where'.

I'm using sql server 2008 if that makes a difference...

Ric

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-08 : 07:31:01

[code]
update o
set o.ordertotal = @orderTotal,
@orderTotal = @orderTotal + o.orderamount
from #order o
join #person p
~~where~~ ON o.personid = p.id
[/code]

KH
[spoiler]Time is always against us[/spoiler]

akira220984
Starting Member

13 Posts

Posted - 2010-02-08 : 07:34:36

Thanks khtan.

Issue now is that order total is just summing up all order amount values:

id personID itemID orderamount ordertotal orderdate
----------- ----------- ----------- ---------------------- ---------------------- -----------------------
1 1 2 15.75 15.75 2010-02-08 12:27:58.260
2 1 3 7.09 22.84 2010-02-08 12:27:58.260
3 3 2 15.75 38.59 2010-02-08 12:27:58.260
4 2 1 10.5 49.09 2010-02-08 12:27:58.260

I have written the following select statement which works perfectly, I was looking to do this in an update:

SELECT #order.personid, SUM(orderamount) as 'Total Customer Order' FROM #order
inner join #person on #order.personid = #person.id
GROUP BY #order.personid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-08 : 07:39:36

[code]
update o
set ordertotal = t.[Total Customer Order]
from #order o
inner join
(
SELECT #order.personid, SUM(orderamount) as [Total Customer Order]
FROM #order
inner join #person on #order.personid = #person.id
GROUP BY #order.personid
) t on o.personid = t.personid
[/code]

KH
[spoiler]Time is always against us[/spoiler]

akira220984
Starting Member

13 Posts

Posted - 2010-02-08 : 07:47:14

Amazing, been looking at my code for a while now and could not see past it! Thanks a lot, works superbly :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources