MySQL query issue

Question

This is my query:

  SELECT count(*) as total, dp.name,dp.id,dp.description, dp.avatar 
  FROM `doctors` d  
  right join departments dp on d.department_id = dp.id 
  group by d.department_id

I have to tables: doctors and departments. I want to extract the total number of doctors from each department. This query works fine, it returns me all of the deparments, which have a doctors, but not which does not have. Somehow I want to show all of the departements and a total, which represents the doctors whose belong to a department. How can i do that ?

This is the doctor table: enter image description here and this is the departments table

have a look at this question that will explain you about the different ways of doing join: [link](http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join). Also can you show us an example of your data in the two tables? — Lelio Faieta, May 17 '15 at 08:43

score 0 · Answer 1 · answered May 17 '15 at 08:49

0

 SELECT (SELECT count(*) FROM doctors AS d WHERE d.id = dp.id) as total, dp.name,dp.id,dp.description, dp.avatar 
FROM departments dp

there are multiple ways of doing this ofcourse, I would do it like this

i dont think you need to join you just need a count of all the doctors and you dont do anything with the rest of the information

answered May 17 '15 at 08:49

lauw

545
3
15

thx but this returns me at total column just 1 or 0, but i have more then one doctors which belongs to a deparment. Check my post again, I updated it , thx – George Moldovan May 17 '15 at 08:52
your query is right except the where part: WHERE d.department_id = dp.id, Can i do it with join instead of select in select? – George Moldovan May 17 '15 at 09:13

score 0 · Answer 2 · answered May 17 '15 at 09:11

In my couple of decades working with SQL I've never used right joins. I've always found the LEFT join easier to read. I also try and return the columns in order of the highest to lowest level of detail and finish with sum's and counts. It reads a lot better.

Try this:

SELECT dp.id,dp.name,dp.description, dp.avatar,count(*) as total
FROM departments dp
    LEFT JOIN doctors d  
        on d.department_id = dp.id 
GROUP BY dp.id,dp.name,dp.description, dp.avatar

You must always group by every column within your select clause except those that are an aggregation (e.g. sum,count) or a constant.

Abhishekh Gupta · Accepted Answer · 2015-05-17T09:22:32.100

0

You can give this a try:

SELECT 
  (SELECT count(*) FROM doctors d WHERE d.department_id = dp.id) AS total, 
  dp.* 
FROM departments AS dp

And if you want to use JOIN then try this:

SELECT 
  COUNT(d.department_id) AS total,
  dp.*
FROM departments AS dp
  LEFT JOIN doctors AS d ON dp.id = d.department_id
GROUP BY dp.id

edited May 17 '15 at 09:22

answered May 17 '15 at 09:13

Abhishekh Gupta

6,206
4
18
46

MySQL query issue

3 Answers3