sql - 使用 group by 查找计数

Question

我有一个学生表如下

Rajesh Kumar
Nagendra Prasad
Bharath kumar
Raghav Kumar
Abhnav Bindhra

我需要编写一个查询，该查询将计算姓名为“kumar”和“Prasad”的学生人数。

Kumar   3
Prasad  1

我怎么做？我尝试了以下查询，但我怀疑 groupby 子句的位置？

 Select Name,count(Name) from Student where 
    Name LIKE  ('%Kumar%')
    or Name LIKE  ('%Prasad%')
    group by ???

Answer 1

select  FindName 
,       count(*)
from    (
        select  case
                when Name like '%Kumar%' then 'Kumar'
                when Name like '%Prasad%' then 'Prasad'
                end as FindName
        from    Student
        ) as SubQueryAlias
where   FindName is not null
group by
        FindName