mysql - 分组依据、SUM 和 COUNT

Question

寻找分组和计数的解决方案。我有一个工作的 SQL，它计算每个值的打开记录:

SELECT tech_query.assigned_to_group, COUNT(tech_query.assigned_to_group) AS qty
  FROM tech_query
 WHERE tech_query.status <> 9 
   AND tech_query.status <> 3
GROUP BY tech_query.assigned_to_group
ORDER BY tech_query.assigned_to_group

结果:

Name:           | qty
-----------------------
ME-MS           | 5
MU-TA-AAA       | 4
MU-TA-BBB       | 2
MU-TA-CCC       | 3

现在我也喜欢合并以MU%开头的Name数据，sum这个值MU%的数据 得到如下结果:

Name:           | qty
-----------------------
ME-MS           | 5
MU              | 9

想知道是否有人可以提供帮助:-)

Answer 1

你需要 mu like 的条件。试试这个查询:

SELECT (case when tech_query.assigned_to_group like "MU%" then "MU"
             else tech_query.assigned_to_group end) as Name,
       COUNT(tech_query.assigned_to_group) AS qty
  FROM tech_query
 WHERE tech_query.status <> 9 
   AND tech_query.status <> 3
GROUP BY name
ORDER BY name