accumulated total in table

[Darin]

I have a stored procedure that i am trying to update a table coming from
a bunch of different calculations.

Main issue:

i have a temporary table that has item, count, acc_count.
i populate the item and count with values, but i need the acc_count to
be accumulation of the count in descending count value:

Item count acc_count
itemA 3 32
itemB 10 25
itemC 4 29
itemD 15 15

I tried to do an update but the update won't let me do an ordered by in
the sub-query for my sum:

update #tmp_table set acc_count=isnull((select sum(r1.count)
from #tmp_table as r1 where r1.count<=#tmp_table.count and
r1.item<=#tmp_table.item ORDER BY r1.count DESC, r1.item ASC),0)

Any ideas?

Darin

*** Sent via Developersdex http://www.developersdex.com ***

[Darin]

Nevermind - i got it working. i needed the GROUP BY and the TOP 1 in the
SELECT SUM statement.

the obvious was missed

[Darin]

Cancel my saying i got it working - i didn't. The summation didn't work
with the group and order by because i don't want any grouping - i just
want one total for all items that have a greater count than it item it
is on.

[Rich Dillon]

Darin,

Here's the idea:

UPDATE #tmp_table
SET acc_count = (
SELECT SUM([count])
FROM #tmp_table AS t
WHERE t.[count] > #tmp_table.[count] OR
(t.[count] = #tmp_table.[count] AND t.item <= #tmp_table.item));

I'd recommend also avoiding the use of keywords like "count" for column
names.

Hope that helps,
Rich

"Darin" <darin_nospam@nospamever> wrote in message
news:#rMribsT...@TK2MSFTNGP06.phx.gbl...

[Erland Sommarskog]

Darin (darin_nospam@nospamever) writes:
> I have a stored procedure that i am trying to update a table coming from
> a bunch of different calculations.
>
> Main issue:
>
> i have a temporary table that has item, count, acc_count.
> i populate the item and count with values, but i need the acc_count to
> be accumulation of the count in descending count value:
>
> Item count acc_count
> itemA 3 32
> itemB 10 25
> itemC 4 29
> itemD 15 15

Beware that SQL Server does not have any good syntax for this. You can use
a subquery like:

UPDATE tbl
SET acc_ccount = (SELECT SUM([count])
FROM tbl b
WHERE b.item <= a.item)
FROM tbl b

But performance will be awful if there are lots of rows. The execution
time will grow expotentially. A cursor may be a better option.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx