CODEWITHSUNDEEP
sqlsql-serveraggregate-functions
mtirtapradja
mtirtapradja

Reputation: 139

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.

enter image description here

enter image description here

And I want the resulting table

enter image description here

Upvotes: 2

Views: 46

Answers (1)

Fahmi
Fahmi

Reputation: 37473

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

Upvotes: 3

Related Questions