I have a database with thousands of data. The name of the table is Person11
I need to select concatenated name, JobTitle and Salary of the people who have a Cat value of N and whose salary is at least 30 percent higher than the average salary of all people with their JobTitle and a Cat value of N. The three column headings should be Name, JobTitle and Salary. The rows should be sorted in traditional phonebook order.
So far this is my code:
SELECT INITCAP(Fname || ' ' || Lname) AS Name,
INITCAP(JobTitle) AS JobTitle, Salary
WHERE Upper(Cat) = 'N'
AND Salary >= 1.30 * (
Where upper(Cat) = 'N')
ORDER BY upper(Lname), upper(Fname);
you need add codition with
SELECT Initcap(Fname || ' ' || Lname) AS Name, Initcap(JobTitle) AS JobTitle, Salary FROM Person11 p WHERE Upper(Cat) = 'N' AND Salary >= 1.30 * ( SELECT Avg(salary) FROM Person11 WHERE Upper(Cat) = 'N' AND JobTitle = p.JobTitle) ORDER BY Upper(Lname), Upper(Fname);