时间戳

首页 > 解决方案 > SQL Server Count with case when

sql-server - SQL Server Count with case when

问题描述

我正在使用以下查询将技能值放入相应的类别中。

select distinct
case 
when skilldescript like 'cspace%' then 'CSPACE'
when skilldescript like 'gen%' then 'GENERAL'
when skilldescript like 'rope%' then 'ROPE'
when skilldescript like 'sct%' then 'SCT'
when skilldescript like 'trench%' then 'TRENCH'
end as Skill_Category
from skillTable

查询给了我以下结果：

Skill_Category
--------------
CSPACE
GENERAL
ROPE
SCT
TRENCH

我现在想要完成的是让查询返回每个类别下有多少不同的技能集。所以结果应该是：

Skill_Category   Count
----------------------
CSPACE             11
GENERAL             5
ROPE               20
SCT                10
TRENCH              3

我尝试使用 count() 或 sum()，但它们只选择单个行数。

标签： sql-server

解决方案

您必须按 CASE 表达式和计数进行分组：

select 
  case 
    when skilldescript like 'cspace%' then 'CSPACE'
    when skilldescript like 'gen%' then 'GENERAL'
    when skilldescript like 'rope%' then 'ROPE'
    when skilldescript like 'sct%' then 'SCT'
    when skilldescript like 'trench%' then 'TRENCH'
  end as Skill_Category,
  count(*) [Count]
from skillTable
group by case 
  when skilldescript like 'cspace%' then 'CSPACE'
  when skilldescript like 'gen%' then 'GENERAL'
  when skilldescript like 'rope%' then 'ROPE'
  when skilldescript like 'sct%' then 'SCT'
  when skilldescript like 'trench%' then 'TRENCH'
end

或者：

select Skill_Category, count(*) [Count]
from (
  select 
    case 
      when skilldescript like 'cspace%' then 'CSPACE'
      when skilldescript like 'gen%' then 'GENERAL'
      when skilldescript like 'rope%' then 'ROPE'
      when skilldescript like 'sct%' then 'SCT'
      when skilldescript like 'trench%' then 'TRENCH'
    end as Skill_Category,
    count(*) [Count]
  from skillTable
) t
group by Skill_Category

推荐阅读