sql - 如何对低于指定值的计数结果求和？

Question

例如，我有一个名为 Cities 的表：

+----+----------+  
| ID |   City   |  
+----+----------+  
| 1  | New York |  
|  2 | Chicago  |  
|  3 | Seattle  |  
|  4 | New York |  
+----+----------+

当我使用查询时：

SELECT Cities.city, Count(Cities.City) AS CitiesCount
FROM Cities
GROUP BY Cities.city;

我的结果是：

+----------+-------------+
|   City   | CitiesCount |
+----------+-------------+
| Chicago  |           1 |
| New York |           2 |
| Seattle  |           1 |
+----------+-------------+

我想要做的是将 CitiesCount = 1 的所有结果汇总到“其他”行中，如下所示

+----------+-------------+
|   City   | CitiesCount |
+----------+-------------+
| New York |           2 |
| Other    |           2 |
+----------+-------------+

我相信我需要使用联合或子查询来做到这一点？

标签： sql

Answer 1

您可以进行条件聚合：

select (case when cnt = 1 then 'others' else city end), sum(cnt)
from (select city, count(*) as cnt
      from table t1
      group by city
     ) t
group by (case when cnt = 1 then 'others' else city end);