locked
SQL Query Clarification RRS feed

  • Question

  • User1421620300 posted

    Can someone help me independently solve this solution so that i will have a strong understanding of how to interpret the following testing question. The question itself seems more untypical than the actual resulting output.  I am only near junior status in SQL queries. Please help! Markus.

    Given two tables, Employee and Department, generate a summary of how many employees are in each
    department. Each department should be listed, whether they currently have any employees or not. The
    results should be sorted from high to low by number of employees, and then alphabetically by department
    when departments have the same number of employees The results should list the department name followed by the employee count.

    Sample DataBase:

    https://ibb.co/HDZPw3n

    Sample Output
    Executive 2
    Technical 2
    Production 1
    Management 0
    Resources 0

    Monday, April 13, 2020 6:11 PM

Answers

  • User-18289217 posted

    I am not quite sure that it will help you to learn what you study but here we go

    SELECT * FROM
    (
    SELECT 
    	D.Name AS DepartmentName, COUNT(E.Id) OVER(PARTITION BY E.Department_ID) AS EmployeesNumber
    FROM
    	Department AS D
    LEFT JOIN
    	Employee AS E
    ON
    	D.Id = E.Department_ID
    ) AS Dept 
    GROUP BY 
        DepartmentName, EmployeesNumber 
    ORDER BY 
        DepartmentName ASC

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 13, 2020 7:59 PM