Nesting SELECT Statements › Using an Aggregate Function in a Nested SELECT Statement

Using an Aggregate Function in a Nested SELECT Statement

You can use an aggregate function in a nested SELECT statement when you want to compare a value in a table with another value derived through an aggregate function.

How It's Done

You want to see job IDs with current salaries that are higher than the average salary for all jobs. To do this, you use the POSITION table and enter:

select job_id, salary_amount
       from position
       where salary_amount >
             (select avg(salary_amount)
                     from position);

This statement first finds the average salary for all jobs in the POSITION table and then looks at all jobs to see which exceed that average.

The result looks like this:

JOB_ID   SALARY_AMOUNT
------   -------------
  4734        53665.00
  5555        76440.00
  4700        59488.00
  4023        74776.00
  5890        68016.00
  5890        66144.00
  6021       111593.00
  4734        57824.50
  5555        76961.00
  6004       110448.00
  4666        85280.00
  4700        59280.00
  5110        56977.80
  5555        54184.00
  5555        70720.00
  8001       117832.68
  9001       146432.00
  6011        94953.52
  4700        53665.56

19 rows processed

Enter the subquery SELECT statement alone to check these results by looking at the average salary itself.