Query BETWEEN TWO TABLES OUTPUT (MYSQL)

two table EMPLOYEE and Department EMPLOYEE’s fields are ID,Name, Salary ,DEPT_ID(foreign key to department table) DEPARTMENT’S fields are id,NAME,LOCATION

VALUES OF EMPLOYEE TABLE WILL Be

Values OF DEPARTMENT TABLE WILL BE

Output from these table should be

DEPARTMENT_Name should be alpabetically within their count If are there same Count DEPARTMENT_Name should appear in alpabetically and count will be desc order

EMPLOYEE TABLE Values

id   name        salary        dept_id
1    Candice     4685          1
2    Julia       2559          2 
3    Bob         4405          4 
4    Scarlet     2305          1 
5    Ileana      1151          4 

Department TABLE Values

id   name           location
1    Executive      Sydney 
2    Production     Sydney 
3    Resources      Cape Town 
4    Technical      Texas 
5     Management    Paris 

OUTPUT DATA SHOULD BE

DEPARTMENT_Name   Count_OF_EMPLOYEE_SAME_DEPARTMENT
Executive         2,
Technical         2,
PRODUCTION        1,
MANAGEMENT        0,
RESOURCES         0

Answer

For what you want to show all departments even if there are no employees is a LEFT JOIN. So, start with the department table (alias “d” in the query) and LEFT JOIN to the employee table (alias “e”). using shorter alias names that make sense with context makes readability easier.

Now, you have the common “count()” which just returns a count for however many records are encountered, even if multiple in the secondary (employee) table based on common ID. In addition to count(), I also did a sum of the employee salary just for purposes that you can get multiple aggregate values in the same query.. Use it or don’t, just wanted to present as an option for you.

Now the order. You want that based on the highest count first, so the COUNT(*) DESC (descending order) is the first sorting. Secondary is the department name to keep alphabetized if within the same count.

select
        d.`name` Department_Name,
            d.Location,
        count(*) NumberOfEmployees,
        sum( coalesce( e.salary, 0 )) as DeptTotalSalary
    from
        Department d
            left join employee e
                on d.dept_id = e.id
    group by
        d.`name`
    order by
        count(*) desc,
        d.`name`