I am trying to list all the tutor names whose salary is greater than the average salary and show how much the salary is greater than by

Question

The part thats giving me the most trouble is showing how much greater by is the salary I don't know how to do it

This is the code I have write down atm for the tutor's salary which is greater than the average just need to show how much greater by :

SELECT tName, Salary
FROM Tutor
WHERE salary > (SELECT avg(Salary) FROM Tutor;

Which SQL dialect is this? Can you use two queries with a variable - or does it have to be a single query? — Dai, Sep 04 '17 at 22:58
Also, try to avoid Correlated Subquries (a SELECT statement inside of another SELECT clause or WHERE clause), but normal Subqueries (a SELECT inside a FROM) are okay. — Dai, Sep 04 '17 at 22:59

score 0 · Accepted Answer · answered Sep 04 '17 at 23:00

0

You are close - one possible syntax is:

SELECT tName, Salary - ((SELECT avg(Salary) FROM Tutor) as SalaryDiff
FROM Tutor
WHERE Salary > (SELECT avg(Salary) FROM Tutor;

Depending on your SQL dialect there may be better ways of doing this! See this question for alternative methods.

answered Sep 04 '17 at 23:00

Ken Y-N

14,644
21
71
114

tyvm I learnt something from this query I can do now tyvm man Bless. – Anthony Jones Sep 04 '17 at 23:06

score 0 · Answer 2 · answered Sep 04 '17 at 23:49

An alternative is to use a variable to avoid a potentially inefficient query execution plan:

DECLARE @avgSalary money
SELECT @avgSalary = AVG( salary ) FROM Tutor

SELECT
    tName,
    salary,
    @avgSalary,
    salary - @avgSalary AS salaryDiff
FROM
    Tutor
WHERE
    salary > @avgSalary

I am trying to list all the tutor names whose salary is greater than the average salary and show how much the salary is greater than by

2 Answers2