Oracle SQL query using group by clause

Question

I've two tables

CREATE TABLE DBTEST.EMP
(
  EMP_ID   NUMBER,
  NAME     VARCHAR2(20 BYTE),
  SALARY   NUMBER,
  DEPT_ID  NUMBER,
  LOC_ID   NUMBER
)

CREATE TABLE DBTEST.LOC
(
  LOC_ID       NUMBER,
  CODE         VARCHAR2(3 BYTE),
  DESCRIPTION  VARCHAR2(100 BYTE)
)

sample data

EMP_ID  NAME        SALARY  DEPT_ID LOC_ID
1       Timmins     180000  1       1
2       Lauchnor    180000  1       1
4       Anderson            4       1
11      Pitcher     116000  3       2
17      Hedrick     182000  3       2
25      Mandurino   182000  2       1
69      Frenzel     62000   4       3



LOC_ID  CODE    DESCRIPTION
1       UT      Utah
2       CA      California
3       EU      Europe

I want to list employees with below-average salaries for their location? Note: this is a group by example.

I hope you understand it.

Thanks

haha, no. I was looking at a few samples of group by clause and came up with this. The query that was written by the author of the article is wrong and I could not figure out how to fix it — Ali
– Ali, Commented Dec 31, 2011 at 21:51
This is what author wrote which is totally wrong SELECT E.NAME FROM EMP E WHERE E.SALARY < ( SELECT MAX(AVG(SALARY)) FROM EMP E1,LOC L WHERE E1.LOC_ID = L.LOC_ID GROUP BY L.LOC_ID) GROUP BY E.LOC_ID — Ali
– Ali, Commented Dec 31, 2011 at 21:52

ziesemer · Accepted Answer · 2011-12-31 22:19:31Z

2

How about something like this?

Select emp.*
  From DBTEST.EMP emp,
    (Select Avg(Salary) avg_salary, loc_id From DBTEST.EMP Group By LOC_ID) averages
  Where averages.loc_id = emp.loc_id
    And emp.salary < averages.avg_salary;

This meets your requirement of using a Group By, but if given the choice, I would have done without, like this:

Select *
  From DBTEST.EMP emp
  Where salary <
    (Select Avg(salary) From DBTEST.EMP emp2 Where emp2.loc_id = emp.loc_id);

This has been tested, and both queries return the following:

"EMP_ID","NAME","SALARY","DEPT_ID","LOC_ID"
"2","Lauchnor","180000","1","1"
"1","Timmins","180000","1","1"
"11","Pitcher","116000","3","2"

edited Dec 31, 2011 at 22:19

answered Dec 31, 2011 at 21:46

ziesemer

28.9k9 gold badges89 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ali Over a year ago

Not totally true but probably gives me the idea of how to deal with it.Thanks

Ali Over a year ago

Your query gives this result. I hope you understand -------First Record ---------- 2 Lauchnor 180000 1 1 180666.666666667 1 -------Second Record ---------- 1 Timmins 180000 1 1 180666.666666667 1 -------Third Record ---------- 11 Pitcher 116000 3 2 149000 2

ziesemer Over a year ago

@Ali - what do you think is wrong with these results? Each of these 3 employees is below the salary average for their expected location.

ziesemer Over a year ago

@Ali - I'm way ahead of you - was already editing my answer to include an example without the Group By. No employee of location #3 is shown, as it's difficult to be below average when you're the only one being compared against. (Frenzel is all alone in Europe.)

David Faber · Accepted Answer · 2015-01-23 03:28:46Z

0

It might be easier to use AVG() as a window function (analytic function in Oracle lingo) in this situation:

SELECT * FROM (
    SELECT emp_id, name, salary
         , dept_id, loc_id
         , AVG(salary) OVER ( PARTITION BY loc_id ) AS avg_salary
      FROM emp
) WHERE salary < avg_salary;

Please refer to SQL Fiddle demo here.

answered Jan 23, 2015 at 3:28

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

Collectives™ on Stack Overflow

Oracle SQL query using group by clause

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related