find the count using group by

Question

I have a students table as below

Rajesh Kumar
Nagendra Prasad
Bharath kumar
Raghav Kumar
Abhnav Bindhra

I need to write a query which will the count of students with names having "kumar" and "Prasad".

Kumar   3
Prasad  1

How do i do that? I tried with the following query, but i am doubtful on what to place for groupby clause?

 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

Answer 2

You may try the following SQL query:

select SUBSTRING(name, CHARINDEX(' ', name) + 1, LEN(name)) as lname, count(name)
from Student
where name like '%kumar%' or name like '%Prasad%'
group by SUBSTRING(name, CHARINDEX(' ', name) + 1, LEN(name));

If CHARINDEX does not work, you may try to use any SQL function to return the index of <space>.

Please consider this as a starting point and not as a copy-paste solution.