Use multiple but identical aggregate function

Question

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

please provide some sample data with your desired output in tabular format — Fahmi
– Fahmi, Commented Oct 31, 2020 at 4:21
Few quick things would be helpful. Firstly, you tagged sql-server in the question but as per your question it is MySQL. They are different Databases, so would suggest to update the tag. Secondly, if you can provide sample input & output datasets, it will help us to provide an answer faster. — Somy
– Somy, Commented Oct 31, 2020 at 4:21

Fahmi · Accepted Answer · 2020-10-31 04:33:53Z

3

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

edited Oct 31, 2020 at 4:33

answered Oct 31, 2020 at 4:23

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mtirtapradja Over a year ago

okay thankyou very much, i've tried it and it work. Thanks @fahmi

Fahmi Over a year ago

@mtirtapradja, happyy to help you. Please don't forget to mark it as an accepted answer :)

Collectives™ on Stack Overflow

Use multiple but identical aggregate function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related