SQL -如何与重复计数结果相加
SQL -如何与重复计数结果相加
提问于 2020-10-15 04:03:41
我对我的查询有一些问题。
我想对员工位置进行分类，并根据区域对其进行计数，这里是我的查询：
SELECT
(CASE
WHEN location = 'India' THEN 'Asia'
WHEN location = 'Italy' THEN 'Europe'
WHEN location = 'Singapore' THEN 'Asia'
WHEN location = 'Australia' THEN 'Australia'
ELSE NULL
END) AS Region,
COUNT(location) AS Total_person
FROM
Location_tbl
GROUP BY
location结果是这样的：
- Asia = 1
- Europe =1
- Asia=1
- 澳大利亚=1
G 210
我想要的结果是：
- Asia = 2
- Europe =1
你们能帮我修复我的查询吗?这样我就能得到我想要的结果了。
回答 3
Stack Overflow用户
回答已采纳
发布于 2020-10-15 04:10:30
您需要在group by中添加自定义组-
select
CASE WHEN location in( 'India','Singapore') THEN 'Asia' WHEN location = 'Italy' THEN 'Europe' WHEN location = 'Australia' THEN 'Australia' END as Region,
count(location) as Total_person from Location_tbl
group by CASE WHEN location in( 'India','Singapore') THEN 'Asia' WHEN location = 'Italy' THEN 'Europe' WHEN location = 'Australia' THEN 'Australia' ENDStack Overflow用户
发布于 2020-10-15 04:16:36
select
Region,
count(Region)
from
(select (CASE WHEN location = 'India' THEN 'Asia' WHEN location = 'Italy' THEN 'Europe' WHEN location = 'Singapore' THEN 'Asia' WHEN location = 'Australia' THEN 'Australia' ELSE null END) as Region
from Location_tbl) tab
group by
tab.RegionStack Overflow用户
发布于 2020-10-15 04:36:57
SELECT
location,
CASE
WHEN location = 'India' or 'Singapore' THEN 'Asia'
WHEN location = 'Italy' THEN 'Europe'
WHEN location = 'Australia' THEN 'Australia'
ELSE NULL
END AS Region
FROM
Location_tbl
GROUP BY
location
ORDER BY
SUM(location);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/64364678
复制相关文章
相似问题
腾讯云开发者
