CODEWITHSUNDEEP
mysql
this_is_cat
this_is_cat

Reputation: 138

MySql Command Line Beginner Advice

I'm a complete beginner to MySql and I'm struggling with an exercise at the moment. I have a table of employees who work in different departments, and their earnings etc.; I'm stuck on the following question:

Find those employees who work more than the average hours worked in their department.

I tried: 

    Select * from details where hours > (select avg(hours) from details);

but that just gave me the employees who work more than the average of all employees. So I changed it to:

    Select * from details where hours > (select avg(hours) from details group by department);

but that just resulted in

    Error 1242 <21000> returns more than one row

Can anyone help point me in the right direction please? I've attached a picture of the code for the table.

Table Image

Upvotes: 1

Views: 56

Answers (2)

Eduard Uta
Eduard Uta

Reputation: 2607

The error you encountered was because the condition (where hours > (subquery) ) expects a scalar value (only one value) and you were returning more (one avg / group - this is because of the group by clause in the subquery). By filtering in the subquery only for the parent department there will be only one avg. Like this:

Select * 
from details d
where d.hours > (select avg(hours) 
    from details 
    where department = d.department 
    group by department);

Upvotes: 4

juergen d
juergen d

Reputation: 204746

Join on the average hours for every department

Select d.* 
from details d
join
(
   select department, avg(hours) as avg_hours
   from details 
   group by department
) tmp on d.department = tmp.department
     and d.hours > avg_hours

Upvotes: 1

Related Questions