Group By And Sum

I am having trouble getting the sum of enrollment where the business_code is the same. My code is the following: SELECT DISTINCT lb.building_code , lb.bus_code, gl.building_name,

Solution 1:

Remove the gl.building_name, gl.enrollment from the GROUP BY clause:

SELECT 
  lb.building_code ,  
  lb.bus_code, 
  gl.bus_name, 
  SUM(gl.enrollment) AS enrollment 
FROM table1 AS gl 
RIGHT OUTER JOIN table 2AS lb ON gl.building_key = lb.building_key
where gl.bus_name = 'Business'and gl.year_cd = 2010GROUPBY lb.building_code,  lb.bus_code, gl.bus_name;

Solution 2:

SELECT lb.building_code,  
         lb.bus_code, 
         gl.bus_name,       
         SUM(gl.enrollment) AS enrollment 
  FROM table1 AS gl 
  RIGHT OUTER JOIN table 2AS lb ON gl.building_key = lb.building_key
  where gl.bus_name = 'Business'and gl.year_cd = 2010GROUPBY lb.building_code, 
           lb.bus_code, 
           gl.bus_name

Solution 3:

I would consider two re-writes. One, if you meant this to be an outer join (so include rows from table2 that aren't in table1), change the order, make it a left join, move the where clauses for table1 into the join clause, remove the distinct, and remove the non-grouped column from the group by:

SELECT lb.building_code, lb.bus_code, gl.building_name, 
  gl.bus_name, SUM(gl.enrollment) AS enrollment 
FROM dbo.table2 AS lb
LEFT OUTER JOIN dbo.table1 AS gl 
  ON gl.building_key = lb.building_key
  AND gl.bus_name = 'Business'AND gl.year_cd = 2010GROUPBY lb.building_code, lb.bus_code, gl.building_name, gl.bus_name;

(For the vast majority of people, a LEFT JOIN is far more intuitive than a RIGHT JOIN.)

If you really don't expect to have any rows from table2 that aren't in table1, then don't write this as an outer join in the first place:

SELECT lb.building_code, lb.bus_code, gl.building_name, 
  gl.bus_name, SUM(gl.enrollment) AS enrollment 
FROM dbo.table2 AS lb
INNER JOIN dbo.table1 AS gl 
  ON gl.building_key = lb.building_key
WHERE gl.bus_name = 'Business'AND gl.year_cd = 2010GROUPBY lb.building_code, lb.bus_code, gl.building_name, gl.bus_name;

Solution 4:

Try

Since you are using right outer join so don't forget to add IsNull in SUM aggregator function to handle the unmatched data from table 2

SELECT lb.building_code ,  lb.bus_code, gl.bus_name, SUM(Isnull(gl.enrollment,0)) AS enrollment 
  FROM table1 AS gl 
  RIGHT OUTER JOIN table 2AS lb ON gl.building_key = lb.building_key
  where gl.bus_name = 'Business'and gl.year_cd = 2010GROUPBY lb.building_code ,  lb.bus_code, gl.bus_name