Get sum of all rows in each row

Question

Is it possible to get sum of all rows in each row. Example

Rows | TotalCount
  1  | 20
  2  | 30
  3  | 10
  4  | 60 

Now, I want get following result.

    Rows | TotalCount
      1  | 120
      2  | 120
      3  | 120
      4  | 120 

If it is possible in SQL server please help.

Answer 1

Use window functions:

select t.*, sum(totalcount) over ()
from t;

In general, window functions are going to be faster than join/aggregation solutions. This is a rather simple case, so the performance might be essentially the same.

Answer 2

Use a sub-query where you do the SUM work:

select rows, (select sum(TotalCount) from tablename) as TotalCount 
from tablename

Or a cross join:

select t1.rows, t2.TotalCount
from tablename t1
  cross join (select sum(TotalCount) as TotalCount from tablename) t2