mysql - SQL group by two fields 计算两个字段

Question

我有一些像这样的 MySQL 代码:

select 
Country, 
if(upper(District) like 'A%', 'A',
if(upper(District) like '%B', 'B','C')) as Field1Type,
count(*) as Salescount
FROM tablename
group by Country, District;

返回的数据如下:

Country District Salescount
--------------------------- 
France   A          10      
France   B          20              
France   C          45          
Germany  A          30          
Germany  B          5           
Germany  C          50

我怎样才能像这样获得每个国家/地区的总数？ (我知道效率不是很高，但它只是一张小 table 。

Country District Salescount CountryTotal
----------------------------------------
France   A       10         75
France   B       20         75
France   C       45         75
Germany  A       30         85
Germany  B       5          85
Germany  C       50         85

Answer 1

你可以对总数使用内部连接

  select 
  a.Country, 
  if(upper(a.District) like 'A%', 'A',
  if(upper(a.District) like '%B', 'B','C')) as Field1Type,
  count(a.*) as Salescount,
  t.CountryTotal
  FROM tablename as a 
  INNER JOIN (
      Select 
      Country, 
      count(*)  CountryTotal
      FROM tablename
      group by Country

  )  on a.Country = t.country
  group by a.Country, a.District;