Use multiple but identical aggregate function

I’m trying to use two identical aggregate function is SQL Server, where I use COUNT(A) with some condition and COUNT(A) with another condition.

For example I want to count all students enrolled in the course ABC, and the second count I want to count all students who have grade A and have enrolled in the same course ABC.

But the result of the COUNT will be on the same table but on different column, is it possible to do this and how can I implement it. Thanks.

And I want the resulting table

Answer

Use conditional aggregation

select name, count(*) as all_student,
       count(case when grade='A' then 1 end) as cnt_A,
       count(case when grade='B' then 1 end) as cnt_B
from course_table c inner join student_table s on c.name=s.course
group by name