subqueries AVG of the SUM result

Question

I'm having an issue with the subqueries. so i want to list employees whose total hours worked is greater than the average total hours worked with all employee. but with this code, it compares the hours worked for one employee with another each hours by another employee. so this is the summarize: employee total hours worked > average of all employee total hours worked --> WHERE hrs_worked > AVG(SUM(HRS_WORKED)) this is what i want.

SELECT employee.emp_id,
       employee.emp_name,
       SUM(action.hrs_worked) AS "Total Hours Worked"
FROM action
INNER JOIN allocation ON action.act_id = allocation.act_id
AND action.emp_id = allocation.emp_id
INNER JOIN employee ON allocation.emp_id = employee.emp_id
WHERE action.hrs_worked >
    (SELECT AVG(action.hrs_worked)
     FROM action)
GROUP BY employee.emp_id,
         employee.emp_name
ORDER BY SUM(action.hrs_worked) DESC

If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Apr 20 '15 at 10:26

score 0 · Answer 1 · answered Apr 20 '15 at 10:46

Should be something like this:

SELECT * 
FROM  Orders
........... 
WHERE Price > (
           SELECT AVG(sum_pr) 
           FROM (
              SELECT SUM(Price) as sum_pr 
              FROM Orders 
              GROUP BY Price
                 )x
               )

score 0 · Answer 2 · answered Apr 20 '15 at 10:54

Let's leave the employee name out of this. The total hours works is:

SELECT a.emp_id, SUM(a.hrs_worked) AS TotalHoursWorked
FROM action a 
GROUP by a.emp_id;

To get the value greater than the avg:

SELECT a.emp_id, SUM(a.hrs_worked) AS TotalHoursWorked
FROM action a 
GROUP by a.emp_id;
HAVING TotalHoursWorked > (SELECT avg(TotalHoursWorked)
                           FROM (SELECT a.emp_id, SUM(a.hrs_worked) AS TotalHoursWorked
                                 FROM action a 
                                 GROUP by a.emp_id
                                ) a2
                           );

You can use join to bring in the rest of the fields that you want.

Based on your query, I don't see how allocation is being used.

subqueries AVG of the SUM result

2 Answers2