CODEWITHSUNDEEP
mysql
S Adrian
S Adrian

Reputation: 39

providing a list with a select query

So I have a table named Medics with name, surname, speciality. The total number of specialities are 17 and 108 doctors.

Speciality: reumatologie, o.r.l, chirurgie, neurolog etc

I need to provide a query to give me the list in this format:

reumatologie - 5, o.r.l - 4, chirugie - 10

Where 5, 4, and 10 are the number of medics from the table. It's more like saying in speciality reumatologie we have 5 medics that correspond to that kind of speciality.

I've tried this concantenating, and more other selects and none of them worked properly. I'm thinking maybe it will work with a case? If yes, how?

The final select query that works is:

select Speciality, Count(distinct m_id) as NRofMedics from Medics group by Speciality;

Upvotes: 0

Views: 69

Answers (2)

vpv
vpv

Reputation: 938

@lanzz is right. However, try something like below- 

select Speciality, Count(distinct name) 
from Medics 
group by Speciality

hope this helps.

Upvotes: 1

Rahul
Rahul

Reputation: 77926

Try this:

select Speciality || '-' || count(distinct name) as 'Med with Speciality' 
from Medics group by speciality

Upvotes: 1

Related Questions