There are 3 tables: professions(id,profession_name) , salary(id,salary) and staffs(id,fio,profession_id, gender) . You need to write a request that determines the position for which the average salary is maximum.

I managed to do only the maximum:

 select professions.profession_name, max(salary.salary), staffs.profession_id from professions, salary, staffs where professions.id = staffs.profession_id and salary.id = staffs.id and salary = (select max (salary) from salary) group by professions.profession_name, staffs.profession_id
  • why salary separate table? - van9petryk
  • van9petruk, this was given in the assignment - mangyst
  • sort by average and take the first of the list - teran

2 answers 2

Required SQL query

 SELECT profession_name, AVG(salary) FROM professions, salary, staffs WHERE staffs.profession_id = professions.id AND salary.id = staffs.id GROUP BY(profession_name) ORDER BY avg DESC LIMIT 1;
  • ERROR: ERROR: the subquery in FROM must have the alias LINE 1: ..._ name, MAX (avg_salary) - mangyst
  • @mangyst, Postgres requires aliases for subqueries inside FROM. Add AS любое_имя immediately after the last bracket. - mymedia
  • ERROR: the column "professions.profession_name" should appear in the GROUP BY clause or be used in the aggregate function. Now I can not understand where to put the group by I tried to put after from but gives an error - mangyst
  • I tested @mangyst on SQLite. In PostgreeSQL, the MAX function works differently. - van9petryk
  • @mangyst rextester.com/JGM48376 - van9petryk

staffs you need to add a salary_id column to the salary_id order to assign a salary id to a person. Next, by completing this query, you will receive an average salary for each profession.

 SELECT professions.profession_name, AVG(s.salary), staffs.profession_id from staffs LEFT JOIN salary as s ON (s.id = staffs.salary_id) LEFT JOIN professions ON (professions.id = staffs.profession_id) where professions.id = staffs.profession_id group by professions.profession_name

To get the maximum average salary, add to the end of the query ORDER BY s.salary DESC LIMIT 1

  • Tables cannot be changed - mangyst
  • @mangyst then wish you the best of luck - Arendach
  • salary.id=staffs.id in the original query is, so obviously Id match in the tables - teran
  • ORDER BY s.salary and where have you forgotten avg ? - teran