sql server - Group by and sum -
i having trouble getting sum of enrollment business_code same. code following:
select distinct lb.building_code , lb.bus_code, gl.building_name, gl.bus_name, sum(gl.enrollment) enrollment table1 gl right outer join table 2 lb on gl.building_key = lb.building_key gl.bus_name = 'business' , gl.year_cd = 2010 group lb.building_code, lb.bus_code, gl.building_name, gl.bus_name, gl.enrollment
current output:
building_code bus_code bus_name enrollment 4581 0000 business 12 4581 0000 business 13 4581 0109 business b 100 4581 0109 business b 120 4581 0209 business c 130 4581 0402 business d 35
desired output:
building_code bus_code bus_name enrollment 4581 0000 business 25 4581 0109 business b 220 4581 0209 business c 130 4581 0402 business d 35
remove gl.building_name, gl.enrollment
group by
clause:
select lb.building_code , lb.bus_code, gl.bus_name, sum(gl.enrollment) enrollment table1 gl right outer join table 2 lb on gl.building_key = lb.building_key gl.bus_name = 'business' , gl.year_cd = 2010 group lb.building_code, lb.bus_code, gl.bus_name;
Comments
Post a Comment