I have query:

SELECT 
    (SELECT employee_title FROM sf_employee WHERE id = T1.worker_ref_id) AS employee_title,
    (SELECT sex FROM sf_employee WHERE id = T1.worker_ref_id) AS sex,
    ((SELECT salary FROM sf_employee WHERE id = T1.worker_ref_id)   bonus_sum) as sum_plus_bonus
FROM
    (SELECT worker_ref_id, SUM(bonus) as bonus_sum
    FROM sf_bonus
    GROUP BY worker_ref_id) AS T1

and the only way I know how to do the grouping is to make this table a subquery in the T2 table in FROM and then group and find the average value for the first and second columns by salary with bonuses:

SELECT employee_title, sex, AVG(sum_plus_bonus) AS avg_salary
FROM
    (SELECT 
        (SELECT employee_title FROM sf_employee WHERE id = T1.worker_ref_id) AS employee_title,
        (SELECT sex FROM sf_employee WHERE id = T1.worker_ref_id) AS sex,
        ((SELECT salary FROM sf_employee WHERE id = T1.worker_ref_id)   bonus_sum) as sum_plus_bonus
    FROM
        (SELECT worker_ref_id, SUM(bonus) as bonus_sum
        FROM sf_bonus
        GROUP BY worker_ref_id) AS T1) AS T2
GROUP BY employee_title, sex

It works, but I have no experience, so it looks a bit strange to me, I think I can do without adding code as in the second option. I am not interested in JOINs and some other functions, I am training subqueries to be confident in using them, if anyone is interested in the task, here it is: https://platform.stratascratch.com/coding/10077-income-by-title-and-gender?code_type=5

It's what i want to do and i got error:

SELECT 
    (SELECT employee_title FROM sf_employee WHERE id = T1.worker_ref_id) AS employee_title,
    (SELECT sex FROM sf_employee WHERE id = T1.worker_ref_id) AS sex,
    AVG((SELECT salary FROM sf_employee WHERE id = T1.worker_ref_id)   bonus_sum) as sum_plus_bonus
FROM
    (SELECT worker_ref_id, SUM(bonus) as bonus_sum
    FROM sf_bonus
    GROUP BY worker_ref_id) AS T1
GROUP BY employee_title, sex

i add AVG and GROUP BY

CodePudding user response：

You can link table from SELECT in FROM section and group it without additional subquery.

SELECT 
 se.employee_title, se.sex, AVG(se.salary   T1.bonus_sum) AS avg_salary
FROM
    (SELECT worker_ref_id, SUM(bonus) as bonus_sum
    FROM sf_bonus
    GROUP BY worker_ref_id) AS T1
    LEFT JOIN sf_employee AS se on se.id = T1.worker_ref_id
GROUP BY
  se.employee_title, se.sex

CodePudding user response：

WITH clause is here just to generate some sample data and, as such, is not a part of the answer:

WITH
    sf_employee (ID, FIST_NAME, LAST_NAME, AGE, SEX, EMPLOYEE_TITLE, DEPARTMENT, SALARY, TARGET, EMAIL, CITY, ADDRESS, MANAGER_ID) AS
        (
            Select 1, 'John', 'Doe', 35, 'M', 'TITLE A', '10', 2000, 2200, '[email protected]', 'Boston', '79, Some Street', 9 From Dual Union All
            Select 2, 'Sam', 'Smith', 25, 'M', 'TITLE C', '10', 1800, 2050, '[email protected]', 'Boston', '321, Some Other Street', 9 From Dual Union All
            Select 3, 'Jane', 'Doe', 31, 'F', 'TITLE B', '20', 1920, 2200, '[email protected]', 'Boston', '79, Some Street', 8 From Dual Union All
            Select 4, 'Ann', 'Chriss', 47, 'F', 'TITLE A', '20', 2100, 2500, '[email protected]', 'Boston', '1110, Some Big Street', 8 From Dual Union All
            Select 5, 'Bob', 'Flint', 54, 'M', 'TITLE A', '30', 2150, 2500, '[email protected]', 'Boston', '1, Some Street', 7 From Dual 
        ),
    sf_bonus (WORKER_REF_ID, BONUS) AS
        (
            Select 1, 175 From Dual Union All
            Select 4, 200 From Dual Union All
            Select 1, 145 From Dual Union All
            Select 5, 250 From Dual 
        )

You could do all the summings and calc the average in main Select. No need for AVG() at all.

main sql:

SELECT    EMPLOYEE_TITLE, SEX, 
          (Sum(e.SALARY)   Sum(Nvl(b.BONUS, 0))) / (Select count(*) From sf_employee Where EMPLOYEE_TITLE = e.EMPLOYEE_TITLE And SEX = e.SEX) "COMPENSATION"
FROM      sf_employee e
LEFT JOIN sf_bonus b ON(b.WORKER_REF_ID = e.ID)
WHERE     Nvl(b.BONUS, 0) <> 0
GROUP BY  EMPLOYEE_TITLE, SEX  

with above sample data the result is:

Regards...