Write a query to find avg in each department and count salary of each department using two tables--CodePudding

Write a query where we want to count the total number of Employees whose salaries exceed Rs. 40,000 in each department but only for departments where more than 5 employees work. what is the command to solve this

 -------- ---------- ------------ ------------ ----------- ------ 
| Emp_no | Emp_name | DOB        | Address    | salary    | D_no |
 -------- ---------- ------------ ------------ ----------- ------ 
|    101 | Harry    | 2002-10-16 | Hogwarts   | 688127000 |   10 |
|    102 | Hermione | 2000-01-07 | Delhi      | 541717000 |   20 |
|    103 | Ron      | 2003-06-09 | Delhi      | 468512000 |    5 |
|    104 | Lupus    | 2000-12-22 | Mumbai     | 366025000 |    5 |
|    105 | Lily     | 2001-12-07 | Rajasthan  | 614922000 |    5 |
|    106 | Granger  | 2005-04-10 | Dumbstrang | 512435000 |   10 |
 -------- ---------- ------------ ------------ ----------- ------ 
6 rows in set (0.00 sec)

mysql> select * from dept;

 --------- ----------- ------------- 
| dept_no | dept_name | dept_allemp |
 --------- ----------- ------------- 
|      10 | Research  |          15 |
|      20 | Cs        |          26 |
|       5 | BSc       |          16 |
|       5 | Army      |          31 |
|       5 | Police    |          18 |
 --------- ----------- -------------

CodePudding user response：

[![enter image description here][1]][1]``` select count(*) as employees_count from employee_tbl where Emp_no in (select Emp_no from employee_tbl where salary>40000) group by D_no having employees_count>5



  [1]: https://i.stack.imgur.com/Q8hgv.png

CodePudding user response：

I made the following assumptions:

The name of the first table is employees
You need salary > 40000 but your salary data has only 9 digit numbers. I am giving you the query as per the question, so feel free to add 3 trailing zeroes here as '400000000' to match your test data case.

Try this:

SELECT D.dept_name FROM
dept D WHERE (SELECT COUNT(*) 
                    FROM employees E 
                    WHERE E.D_no = D.dept_no AND
                            E.salary > 40000) > 5

Here is the fiddle. Please mark the answer as correct if it helps you. Best of luck.