SQL server accumulation, SQL server

SQL server accumulation, SQL server
1. The column in the selected list is invalid because it is not included in the aggregate function or group by clause.

A: When group by is specified, all columns in any non-aggregate expression in the selection list should be included in the group by list, or the group by expression must exactly match the expression in the selection list.

2. Use case when then else end syntax

Select
I. index_name, I. index_def,
Sum (case when w. years = 2014 then w. actual_value else 0 end) as a1,
Sum (case when w. years = 2014 then w. ranking else 0 end) as r1,

Sum (case when w. years = (2014-1) then w. actual_value else 0 end) as a2,
Sum (case when w. years = (2014-1) then w. ranking else 0 end) as r2,

From bm_work w, bm_index I
Where
W. indexdef_id = I. uuid and
W. ent_id = '2c90e4da49514c750149515eb56f0003 'and w. summary_state = 2
Group by I. index_name, I. index_def;

This statement achieves this effect