Ayaz Amir Ayaz Amir - 7 months ago 9
SQL Question

SQL ORACLE Sub query. need to fix my outcomes

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
FROM Person11
WHERE Upper(Cat) = 'N'
AND Salary >= 1.30 * (
Select avg(salary)
FROM Person11
Where upper(Cat) = 'N')
ORDER BY upper(Lname), upper(Fname);


This gives me an output of all the people with 30 percent higher than average salary with their cat value of n.

How can I find all of people with 30 percent higher than average salary of all the people with their
JOB TITLE
and who has a cat value of 'N'?

Answer

you need add codition with JobTitle:

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);
Comments