ORA-00918: column ambiguously defined 00918. 00000 -- ANY SUGGESTION? [closed]

Question

WITH MYCTE AS
(
    SELECT  
        ENGINEERING.ENG_ID, ENGINEERING.NAME,
        ENGINEERING.SALARY, 
        MARKETING.MARKET_ID, MARKETING.NAME,
        MARKETING.SALARY 
    FROM 
        ENGINEERING 
    JOIN 
        MARKETING ON MARKETING.MARKET_ID = ENGINEERING.ENG_ID
)
SELECT 
    EMPLOYEE_ID, SUM(SALARY) TOTAL_SAL
FROM 
    EMPLOYEES S
JOIN 
    MYCTE K ON K.ENG_ID = S.EMPLOYEE_ID
GROUP BY 
    EMPLOYEE_ID

Answer 1

In cte you have duplicate salary column name your query returning error of ambiguously Alias you engineering.salary as engineering_salary and marketing.salary as marketing_salary Or Run this query

WITH MYCTE AS (
SELECT 
ENGINEERING.ENG_ID as eng_id,
ENGINEERING.NAME as engineering_name,
ENGINEERING.SALARY as engineering_salary
,MARKETING.MARKET_ID as market_id,
MARKETING.NAME as marketing_name,
MARKETING.SALARY as marketing_salary
FROM ENGINEERING
JOIN 
MARKETING
 ON MARKETING.MARKET_ID=ENGINEERING.ENG_ID) 

SELECT EMPLOYEE_ID,
SUM(marketing_salary + engineering_salary ) TOTAL_SAL
 FROM EMPLOYEES S 
JOIN 
MYCTE K 
ON K.ENG_ID=S.EMPLOYEE_ID
 GROUP BY EMPLOYEE_ID;