CODEWITHSUNDEEP
sqlsql-server
Chelseajcole
Chelseajcole

Reputation: 557

How to Assign Local Variable to Local Variable in SQL Server

Suppose I have a table numbers:

number  frequency
1        6
2        2
3        8
4        5

In MySQL, we can dynamically assign variable without declare

select 
Number, 
@prev := @count as prevNumber, 
(@count := @count + Frequency) as countNumber
from Numbers

In SQL Server, can we do the same?

I have to declare and then use? 

declare @prev int
declare @counts int
set @prev=0
set @counts=0

select number,
(@prev=@counts) as prevNumber,
(@counts=@counts+frquency) as countNumber from numbers

Obviously above code is incorrect? What is the correct way to do it?

Upvotes: 0

Views: 462

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

The specific statement that sgeddes wants to say is that a SELECT statement in SQL Server can assign values to a variable or return results, but cannot do both. The MySQL code is doing both.

In SQL Server, you would do this using window functions. If you want the running sum up to but not including the current value, just subtract out the current value:

select number,
       sum(frequency) over (order by number) as running_sum,
       (sum(frequency) over (order by number) - frequency) as running_prev_sum
from yourtable;

You can also use a window clause:

sum(frequency) over (order by number rows between unbounded preceding and 1 preceding)

But I find that cumbersome and less clear than just subtracting the current value.

I need to also add that your MySQL is not guaranteed to work. MySQL does not guarantee the order of evaluation of expressions in a SELECT. If you want to fix that, ask another question.

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

SQL Server doesn't support user-defined variables as such. With that said, you can achieve the same result of creating a running sum using a window function:

select number, sum(frequency) over (order by number)
from yourtable

Per the comment below, if you need the previous running sum as well, you can use a subquery with lag and coalesce:

select number, sum(prevnum) over (order by number) as prevNumber, countNumber
from (select *, coalesce(lag(frequency) over (order by number),0) as prevnum, 
                sum(frequency) over (order by number) as countNumber 
      from yourtable) t

This basically just creates multiple running sums, one for the previous amount as well.

Upvotes: 1

Related Questions