你真的不需要 Name 列来计算东西。这应该是正确的：

SELECT T.User_ID, COUNT(*) AS Total, SUM(CASE WHEN T.Language = 'EN' THEN 1 ELSE 0 END) AS EN
FROM (
   SELECT User_ID,
      CASE 
        WHEN Language in ('EN', 'English') THEN 'EN' 
        WHEN Language in ('MY', 'Malay')  THEN 'MY'
        ELSE Language 
      END AS Language
   FROM 'table'
   GROUP BY User_ID, Language
) AS T
GROUP BY T.User_ID

我不知道bigquery，但是关系数据库需要别名子查询，所以我将您的别名改为 T .
后面还有一个多余的逗号 SUM(CASE WHEN Language = 'EN' THEN 1 ELSE 0 END) AS EN,

您需要确保在子查询中添加“name”列。
很明显，一旦你加上名字，你也必须按名字分组。

SELECT
 User_ID, 
 COUNT(Name) AS Total,
 SUM(CASE WHEN Language = 'EN' THEN 1 ELSE 0 END) AS EN,

 FROM (
  SELECT 
  User_ID, 
  Name,

   CASE 
     WHEN Language in ('EN', 'English') THEN 'EN' 
     WHEN Language in ('MY', 'Malay')  THEN 'MY' ELSE Language 
   END AS Language,

  FROM 'table'
  GROUP BY User_ID, Name, Language
 )
GROUP BY User_ID

试试这个。