Semantic of SQL Query for summing up values

Question

I am new to SQL and not getting even simple things. I am wondering if the following code does not add a real column?

select * 
sum(value) over (partition by prename order by NumberOfDays) SumValue
from (select *, row_number() over (order by NumberOfDays) rn from 
tablenames) as t
order by rn 

I thought with this code I would create a new column called SumValue. When I execute the code above it works fine and the column SumValue with all its values is shown but when I try to calcualte with this column, let's say:

ALTER TABLE tablenames
ADD AvValue float; 
UPDATE [DatabaseXY].[dbo].[tablenames]
SET AvH = [SumValue]/[NumberOfDays]

SSMS tells me that SumValue is an invalid column name. Why is that? Even if I refresh or restart SSMS and look into the object explorer there is no column called SumValue.. I guess with my presented code I only create kinda temporary columns but how do I add those values to a real column?

Thank you so much!

Answer 1

No column is added to the table during the select. Adding columns to a table is usually restricted to a lot fewer people, than those that are allowed to select from a table. You can do this as:

ALTER TABLE tablenames
    ADD AvValue float;

MERGE INTO tablenames x
USING (
    select t.* 
         , sum(value) over (partition by prename order by NumberOfDays) SumValue
    from tablenames t
) y
    ON x.<key> = y.<key>
WHEN MATCHED THEN
    UPDATE SET x.AvValue = y.SumValue/y.NumberOfDays

All untested since I don't know what the key for tablenames is.

You can probably calculate AvValue directly in the USING clause, but I'll leave that as an exercise.

EDIT: The implementation of MERGE in SQL-server seems to have several problems, see use-caution-with-sql-servers-merge-statement/ (link provided by Aaron Bertrand in comment. Using an update instead would be something like (still untested):

WITH y AS (
    SELECT t.* 
     , SUM(value) OVER (PARTITION BY prename 
                        ORDER BY NumberOfDays) SumValue
    FROM tablenames t
)
UPDATE y
SET y.AvValue = y.SumValue/y.NumberOfDays;