CODEWITHSUNDEEP
sql
Akhand Chaurasia
Akhand Chaurasia

Reputation: 45

What should be the query for table empsalary(empid,salary,designation) when i want to find the maximum salary of the designation manager and clerk

When i want to print the maximum salary of designation manager and clerk and i apply:

select empid,max(salary) 
from empsalary 
where designation='manager' or designation='clerk'

then it gives error that:

empid is not included in aggegate function or group by

Upvotes: 0

Views: 351

Answers (2)

EylM
EylM

Reputation: 6103

You don't need the empid in the select part of your query. Move the designation field to this part.

SELECT designation, Max(salary)
FROM   empsalary
WHERE  designation = 'manager' OR designation = 'clerk' 
GROUP BY designation ;

The output should be 2 rows (assuming there is at least one record with 'manager' and 'clerk' values).

Upvotes: 0

forpas
forpas

Reputation: 164099

The error means that you are missing a group by clause:

select designation, max(salary) 
from empsalary 
where designation = 'manager' or designation = 'clerk'
group by designation

If you need the ids of clerks and managers with the maximum salary, you must join this query to the table:

select empid, designation, salary
from empsalary e inner join (
    select designation, max(salary) 
    from empsalary 
    where designation = 'manager' or designation = 'clerk'
    group by designation
) g on g.designation = e.designation and g.salary = e.salary

Upvotes: 1

Related Questions