Visit Our UNIX and Linux User Community


SQL Developer JOINS / GROUP BY issue.


 
Thread Tools Search this Thread
Top Forums Programming SQL Developer JOINS / GROUP BY issue.
# 1  
Old 06-10-2011
SQL Developer JOINS / GROUP BY issue.

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

Code:
 
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:

Code:
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 Smilie

Last edited by U_C_Dispatj; 06-10-2011 at 11:08 AM..
# 2  
Old 06-10-2011
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.

Last edited by DGPickett; 06-10-2011 at 05:15 PM..

Previous Thread | Next Thread
Test Your Knowledge in Computers #23
Difficulty: Easy
One Terabyte (1 TB) is equal to 1000 GB.
True or False?

7 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

UNIX joins : facing issue while joining three files

Hello , I have three files : sampleoutput1.txt has columns (in the following order) : hostname ; available patches , available packages sampleoutput2.txt has columns (in the following order) : hostname ; patchwave ; BSID ; Application sampleoutput3.txt has columns (in the following... (10 Replies)
Discussion started by: rahul2662
10 Replies

2. Programming

Sql ORA-00937: not a single-group group function

I'm trying to return only one row with the highest value for PCT_MAX_USED. Any suggestions? When I add this code, I get the ORA-00937 error. trunc(max(decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100))) pct_max_used This is the original and returns all rows. select (select... (3 Replies)
Discussion started by: progkcp
3 Replies

3. Programming

Sql developer how to upload the excel sheet in Oracle table

I have some records to be updated in oracle table. I am using sql developer tool. could any one tell me how to update those records in oracle table. I am having excel sheet with those records. (4 Replies)
Discussion started by: ramkumar15
4 Replies

4. Solaris

Big /etc/group creating issue

I have Solaris-10 with kernel patch 142900-03, Update 7 Sparc server. root@ddlomps40:/# uname -a SunOS ddlomps40 5.10 Generic_142900-03 sun4u sparc SUNW,SPARC-Enterprise root@ddlomps40:/# cat /etc/release Solaris 10 5/09 s10s_u7wos_08 SPARC Copyright 2009 Sun... (5 Replies)
Discussion started by: solaris_1977
5 Replies

5. Shell Programming and Scripting

NFS Mount 16+ group Issue in AIX

Hi, I am trying to read files from NFS mount. The ID with which I am trying to read is part of 16+ groups. This is causing problem in doing change directory to the NFS mount directory. I was able to get some code which does newgrp to change the default group so that I could change... (3 Replies)
Discussion started by: pinnacle
3 Replies

6. Programming

Help with Oracle SQL Developer - Subquery & Join will not work.

Greetings all, Hopefully there is someone out there on this forum who can help with this Oracle SQL Developer issue I have. Here is the code: CREATE OR REPLACE VIEW SALES_OVER_30000_WITH_TOTAL AS SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS EMPLOYEE_NAME, ... (2 Replies)
Discussion started by: U_C_Dispatj
2 Replies

7. Programming

SQL IF-THEN-ELSE issue

Hi All, I have a table with the following columns: sysName, date, time, cpuNum, cpuPercentageBsy There are multiple system names and multiple CPU numbers. I need to produce a report that shows the cpuPercentageBsy for cpuNum's 0 and 1 and then an average of cpuPercentageBsy for all the... (1 Reply)
Discussion started by: pondlife
1 Replies

Featured Tech Videos