CODEWITHSUNDEEP
mysqlsqlgroup-bymaxhaving-clause
Mi88ir
Mi88ir

Reputation: 21

SQL Query using MYSQL

Write a query to display Department_ID and maximum salary of all employees whose Department_ID is greater than 10 and having a maximum salary greater than 20000 for each department. Display the data in descending order with respect to Department_ID.

select Department_ID, max(Salary)
from employees
where Department_ID > 10
having max(Salary) >20000
order by Department_ID DESC;

This is my query but it only displays one result. Since the question asks for each department, I am sure my query is incorrect.

Upvotes: 1

Views: 53

Answers (2)

zip
zip

Reputation: 4061

You are missing the group by

   select Department_ID, max(Salary)
    from employees
    where Department_ID > 10
Group by Department_ID
    having max(Salary) >20000
    order by Department_ID DESC;

Upvotes: 3

GMB
GMB

Reputation: 222492

You need a group by clause to make your query a valid aggregation query (in many RDBMS, you would get a syntax error):

select department_id, max(salary) max_salary
from employees
where department_id > 10
group by department_id
having max(salary) > 20000

Upvotes: 3

Related Questions