分组和和 [英] Group by and sum

问题描述

我无法获得business_code相同的注册总数。我的代码如下：

  SELECT DISTINCT lb.building_code，lb.bus_code，gl.building_name，gl.bus_name，SUM gl.enrollment）AS注册
 FROM table1 AS gl 
 RIGHT OUTER JOIN表2 AS磅ON gl.building_key = lb.building_key 
其中gl.bus_name ='Business'
和gl.year_cd = 2010 
 GROUP BY lb.building_code，lb.bus_code，gl.building_name，gl.bus_name，gl.enrollment 
  

现行产出：

$ $ $ $ $ $ $ $ $ $ $ $ $ $
4581 0000业务A 13
4581 0109业务B 100
4581 0109业务B 120
4581 0209业务C 130
4581 0402业务D 35

所需输出：



  building_code bus_code bus_name登记t 
 4581 0000业务A 25 
 4581 0109业务B 220 
 4581 0209业务C 130 
 4581 0402业务D 35 
  




解决方案

从> gl.building_name，gl.enrollment GROUP BY 子句：

  SELECT 
 lb.building_code， 
 lb.bus_code，
 gl.bus_name，
 SUM（gl.enrollment）AS注册
 FROM table1 AS gl 
 RIGHT OUTER JOIN表2 AS lb ON gl。 building_key = lb.building_key 
其中gl.bus_name ='Business'
和gl.year_cd = 2010 
 GROUP BY lb.building_code，lb.bus_code，gl.bus_name; 
  




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 

解决方案

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;

这篇关于分组和和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！