SQL Developer JOINS / GROUP BY issue.

Applications Programming
1

Am having a nightmare with a certain piece of code.. have tried almost everything and just cannot see what the issue is..

 
CREATE OR REPLACE VIEW TOP_EARNER_PER_LOCATION
AS
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS EMPLOYEE_NAME,
           L.REGIONAL_GROUP AS REGIONAL_GROUP, 
           E.SALARY AS SALARY, 
           J.JOB_ID 
FROM EMPLOYEE E
LEFT OUTER JOIN DEPARTMENT D
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
LEFT OUTER JOIN LOCATION L 
ON L.LOCATION_ID = D.LOCATION_ID
LEFT OUTER JOIN JOB J 
ON J.JOB_ID = E.JOB_ID
GROUP BY E.FIRST_NAME || ' ' || E.LAST_NAME
HAVING J.JOB_ID NOT IN (670,671,672)
;
 
REM #...SELECT STATEMENT TO RUN QUERY GOES HERE

However it doesn't pull through the HIGHEST earner per location, simply a list of earners, locations and salarys.

The query looks as such:

EMPLOYEE_NAME                   REGIONAL_GROUP       SALARY                 
------------------------------- -------------------- ---------------------- 
FRED JAMES                      CHICAGO              950                    
DENISE SOMMERS                  CHICAGO              1850                   
JAMES MURRAY                    DALLAS               750                    
JOHN SMITH                      DALLAS               800                    
DIANE ADAMS                     DALLAS               1100                   
RICHARD LEWIS                   DALLAS               1800                   
DONALD SCOTT                    DALLAS               3000                   
JENNIFER FORD                   DALLAS               3000                   
ALICE JENSEN                    NEW YORK             750                    
MICHAEL DOUGLAS                 NEW YORK             800                    
BARBARA MILLER                  NEW YORK             1300                   
GRACE ROBERTS                   NEW YORK             2875                   
MATTHEW FISHER                  NEW YORK             3000

Any help would be greatly appreciated :slight_smile:

2

Group by should have all variable columns not aggregates, as it is used for aggregates, which you lack. Having is for testing aggregates. What is you general aim?

You might want two queries, derived tables, one with the aggregate and key only, finding top salary per dept and inner join that pathfinder query/derived table to the no-aggregate report query just as a filter on salary= and dept=. Might be many per dept on same high salary. Order by dept, name, maybe.