CODEWITHSUNDEEP
sql-servergroup-byaggregate-functions
Tone
Tone

Reputation: 783

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, gl.bus_name, SUM(gl.enrollment) AS enrollment 
  FROM table1 AS gl 
  RIGHT OUTER JOIN table 2 AS lb ON gl.building_key = lb.building_key
  where gl.bus_name = 'Business'
  and gl.year_cd = 2010
  GROUP BY 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 A    12
4581             0000       Business A    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 A    25
    4581             0109       Business B    220
    4581             0209       Business C    130 
    4581             0402       Business D    35

Upvotes: 1

Views: 105

Answers (4)

Sandeep Kumar
Sandeep Kumar

Reputation: 803

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 2 AS lb ON gl.building_key = lb.building_key
  where gl.bus_name = 'Business'
  and gl.year_cd = 2010
  GROUP BY lb.building_code ,  lb.bus_code, gl.bus_name

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

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 = 2010
GROUP BY 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 = 2010
GROUP BY lb.building_code, lb.bus_code, gl.building_name, gl.bus_name;

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

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 2 AS lb ON gl.building_key = lb.building_key
where gl.bus_name = 'Business'
  and gl.year_cd = 2010
GROUP BY lb.building_code,  lb.bus_code, gl.bus_name;

Upvotes: 1

juergen d
juergen d

Reputation: 204746

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

Upvotes: 1

Related Questions