1

I am using MariaDB,in my sample database I am having two tables department and emp.
I want to display all the department having less than 50 employees.
In this case it should display all the department names.

select  count(a.deptId),b.deptname
    from  emp as a
    right join  department as b on a.deptId = b.deptId
    group by  a.deptId
    having  count(a.deptId) <= 50 ; 

I tried above mentioned query, it is returning correct count for the department but it is leaving the last deptname operations mysteriously.

Can anybody explain me why it is happening?
My understanding : As per right join the right side table's all row will be fetched and matched with the other table. If matches found result will be displayed otherwise NULL will be printed.

+--------+-------------+
| deptId | deptname    |
+--------+-------------+
|    501 | HR          |
|    502 | Accounts    |
|    503 | Development |
|    504 | Operations  |
+--------+-------------+


+--------+--------+------+-------+--------+
| emp_id | salary | name | bonus | deptId |
+--------+--------+------+-------+--------+
|      1 |    850 | NULL |    10 |    501 |
|      2 |    920 | NULL |  NULL |    502 |
|      3 |    550 | NULL |  NULL |    501 |
|      4 |    450 | NULL |     5 |    502 |
|      5 |    800 | NULL |  NULL |    501 |
|      6 |    920 | NULL |  NULL |    502 |
|      7 |    155 | NULL |  NULL |    501 |
|      8 |     50 | NULL |    20 |    501 |
+--------+--------+------+-------+--------+
1

You are grouping by the thing you are counting, not by the department name. Change your group by to: group by b.deptname

Edit:Adding a little flavor text as requested.

Basically what is happening is you are choosing to aggregate one of the values (in this case the counts of the salary) so that it "rolls up", and the group by generally indicates which value you want to do the rolling up by.

It makes some sense to say "I want to group by the number of employees" but you are actually trying to express "I want to return the number of employees grouped by department."

  • it is working, can you please mention the execution steps of the query so that it will be helpful – Narendra Jaggi Mar 8 '16 at 19:39
  • Highly recommend sqlfiddle.com/#!9 for examples FYI – ConstantineK Mar 8 '16 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.