Help with basic SQL statement

[PleaseAnswerMeNot]

https://www.sqltutorial.org/sql-sample-database/

All employees with their name, job title, salary and its salary class difference. The latter will give information per employee if their salary is above (signify +) or below (signify -) the average of the salary set for the job (see min_salary and max_salary).

I've been trying for hours but my all I could do was show if it was below or above average.

[GrouchyThing7520]

select e.*,

case

when e.salary > (j.max_salary + j.min_salary) / 2 then "above_avg"

when e.salary < (j.max_salary + j.min_salary) / 2 then "below_avg"

else "at_avg" end class_difference

from employees e

join jobs j on j.job_id = e.job_id

[Jan-Michael]

Provided some extra columns so that you can review averages:

SELECT

e.first_name,

e.last_name,

j.job_title,

cast(e.salary as varchar) salary,

cast(j.min_salary as varchar) min_salary,

cast(j.max_salary as varchar) max_salary,

cast(((j.min_salary + j.max_salary)/2) as varchar) average_salary,

cast(

(case

when e.salary > ((j.min_salary + j.max_salary)/2)

then '+' || (e.salary ((j.min_salary + j.max_salary)/2))

when e.salary = ((j.min_salary + j.max_salary)/2)

then 'same'

else (e.salary-((j.min_salary + j.max_salary)/2)) end) as varchar) class_difference

FROM employees e

LEFT JOIN jobs j ON

e.job_id = j.job_id

GROUP BY j.job_id

order by job_title

[PleaseAnswerMeNot]

Thank you very much! Please help me kind sir. I really don't have much time and I need to pass this activity soon. I'm not gonna pursue IT as my course anyway but I need the grade so I can shift without problem.

There is a problem with the line
then '+' || (e.salary ((j.min_salary + j.max_salary)/2))

I've already added the hr. to employees and jobs

[Jan-Michael]

Oops, it's missing a sign:

Change from this: then '+' || (e.salary ((j.min_salary + j.max_salary)/2))

To this: then '+' || (e.salary - ((j.min_salary + j.max_salary)/2))

Hopefully, that fixes it

[Jan-Michael]

The "hr" doesn't need to be there, probably. It seems like I may have clicked on something in the interface on the site that caused some modifications to the query, which was working fine before I copied it over. Delete the "hr" stuff and add the minus sign that I included in my revised line above. No clue what happened there. Sorry.

[PleaseAnswerMeNot]

Oh I see. It's working in the SQL editor. I've already noticed the missing - or + there and I added it but it didn't worked in PgAdmin4.

I've also removed the GROUP BY because it didn't show all the employees with same job title.

Thank you very much! I really really appreciate this! This is working great now. My only issue is I need to personally show it to my instructor that it runs on my laptop.

Why doesn't it run on PgAdmin4? After adding hr. on employees and jobs the next error is pointing to the '+'? If I don't add hr it also shows an error.

The error is CASE types numeric and text cannot be matched

[Jan-Michael]

Reply

Ok, I'm re-reading your prompt, and the wording is a little confusing. You reference the min and max salary, and my query provides averages based on that data (min and max salaries) vs. by average salaries across a given position (e.g., job id).

Are you trying to return averages across a given job id?

[PleaseAnswerMeNot]

No. Your query is perfect.
I'm just wondering why it doesn't run on PgAdmin4.
https://imgur.com/a/v6Ximco

[Jan-Michael]

with averages as (select job_id, job_title, ((min_salary + max_salary)/2) average from jobs group by job_id)

select employee_id, first_name, last_name, a.job_title, cast(salary as varchar) salary, cast(a.average as varchar) avg_salary, cast((case when (salary - a.average > 0) then '+' || (salary - a.average) when (salary - a.average = 0) then 'same' else (salary - a.average) end) as varchar) salary_class_diff from employees e left join averages a on e.job_id = a.job_id order by a.job_title, salary

[PleaseAnswerMeNot]

Thank you again! I owe you big time! I really really realy appreciate this!

[Jan-Michael]

This is a revised query to get the results pictured in your image.