Sum rows with no field to group by

I’m using SQL server 2012 and I’m trying to sum rows of a table. Problem is I don’t have a column to group by. Is this possible…? How would you raise the query?

Original table:

+----+------+--------+
| ID | User | Salary |
+----+------+--------+
| 1  | John | 150    |
| 2  | Marie| 120    |
| 3  | Willy| 125    |
| 4  | Juan | 110    |
| 5  |Carlos| 145    |
| 6  | Mike | 115    |
| 7  | Elsie| 100    |
| 8  | Elvie| 500    |
+----+------+--------+

Expected Result:

+----+--------+--------+
| ID |  USER  | SALARY |
+----+--------+--------+
|  1 | 1plus2 |    270 |
|  2 | 1plus3 |    275 |
|  3 | 1to4   |    205 |
|  4 | 1plus8 |    650 |
+----+--------+--------+

Answer

You can define the groups using a table constructor. For instance, for the first two:

select v.new_user, sum(salary)
from t join
     (values ('1plus2', 1), ('1plus2', 2),
             ('1plus3', 1), ('1plus3', 3)
     ) v(new_user, user)
     on t.user = v.user
group by v.new_user;

Just expand the values() clause to add more groups of interest.

Advertisement

Answer