Ayaz Amir - 1 year ago 34

SQL Question

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`

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