Query BETWEEN TWO TABLES OUTPUT (MYSQL)

Question

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

For the future, can you please edit your post and provide text as samples of the data. Easier than pictures. Also, can you show any sample SQL you have attempted. — DRapp, May 10 '20 at 12:19
EMPLOYEE TABLE Values 1,Candice,4685,1 2,Julia,2559,2 3,Bob,4405,4 4,Scarlet,2305,1 5,Ileana,1151,4 Department TABLE Values 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 — Parminder Singh, May 10 '20 at 12:28
As I mentioned, EDIT your EXISTING Post. You can't format the readability of data in a comment. I updated your question with the content. You'll get used to it in the future, and again, show what you have attempted so others can help point out what you may have missed. It's all a learning curve and you get better when you understand WHY something is done, not just here's the answer. — DRapp, May 10 '20 at 12:36

score 0 · Accepted Answer · answered May 10 '20 at 12:31

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`

Query BETWEEN TWO TABLES OUTPUT (MYSQL)

1 Answers1