CODEWITHSUNDEEP
mysqlsql
user2820372
user2820372

Reputation:

How to select the employee who worked the longest hours on project

I have the following assignment table:

+-----------+----------------+-------------+
| ProjectID | EmployeeNumber | HoursWorked |
+-----------+----------------+-------------+
|      1000 |              1 |       30.00 |
|      1000 |              8 |       75.00 |
|      1000 |             10 |       55.00 |
|      1100 |              4 |       40.00 |
|      1100 |              6 |       45.00 |
|      1200 |              1 |       25.00 |
|      1200 |              2 |       20.00 |
|      1200 |              4 |       45.00 |
|      1200 |              5 |       40.00 |
|      1300 |              1 |       35.00 |
|      1300 |              8 |       80.00 |
|      1300 |             10 |       50.00 |
|      1400 |              4 |       15.00 |
|      1400 |              5 |       10.00 |
|      1400 |              6 |       27.50 |
+-----------+----------------+-------------+

In the following table I found the two projects that are over budget, but I must find the employee number of the person that worked the most hours on each project:

CREATE VIEW OVER AS
    SELECT P.Department, P.ProjectID, A.EmployeeNumber, A.HoursWorked   
    FROM project AS P JOIN assignment AS A
    ON P.ProjectID = A.ProjectID
    GROUP BY P.ProjectID
    HAVING MAX(P.maxhours) < SUM(A.hoursworked);

+------------+-----------+----------------+-------------+
| Department | ProjectID | EmployeeNumber | HoursWorked |
+------------+-----------+----------------+-------------+
| Marketing  |      1000 |              1 |       30.00 |
| Marketing  |      1300 |              1 |       35.00 |
+------------+-----------+----------------+-------------+

The correct employeenumber should be 8 for both projects and 75 and 80 hours.

Any idea how to retrieve the MAX hours worked for these individual projects?

Upvotes: 3

Views: 3372

Answers (1)

Bohemian
Bohemian

Reputation: 425228

The easy way is like this:

select * from (
    select ProjectID, EmployeeNumber
    from assignment
    group by 1, 2
    order by sum(HoursWorked) desc
) x
group by 1

See live demo on SQLFiddle.

This works due to mysql's special handling of group by, which allows not all non-aggregate columns to be listed and in such cases returns just the first row encountered for each group.

Upvotes: 2

Related Questions