Exercise 5-10

Using Aggregate Functions › Grouping Information › Exercise 5-10

Exercise 5-10

Try Another

The budget group needs to identify the total salaries for each job in order to determine salary budgets for next year.

Using the POSITION table, enter a SELECT statement to show the sum of salaries for employees by job. Display job ID and sum for each job.

The result looks like this:

JOB_ID (EXPR) ------ ------ 2051 <null> 2053 <null> 2077 118362.84 3333 208170.50 4012 150187.20 4023 74776.00 4025 43888.00 4123 49921.76 4130 45241.94 4560 <null> 4666 85280.00 4700 267386.90 4734 111489.50 5110 56977.80 5555 492650.98 5890 269465.80 6004 110448.00 6011 94953.52 6021 111593.00 8001 117832.68 9001 146432.00 21 rows processed

If your results do not match what you see above, check Review Answers for Chapter 5 for the correct SQL syntax. Remember that result tables may be shortened in this guide.

Things to remember when using GROUP BY

You can use GROUP BY with any aggregate function
You can specify more than one column in the GROUP BY clause
Columns listed in the SELECT statement that are not part of the calculation by an aggregate function must be identified in the GROUP BY clause

Using ORDER BY with Aggregate Functions

When you want to sort a result table based on an aggregate function, you must specify the aggregate function by number (or heading name) rather than specifying the function itself.

How It's Done

Earlier you found the average salary for employees by job using GROUP BY. To sort this result by the average salary, enter:

select job_id, avg(salary_amount)
       from position
       group by job_id
       order by 2;

The result looks like this:

JOB_ID (EXPR) ------ ------ 3333 23130.05 2077 23672.56 4012 37546.80 4025 43888.00 4130 45241.94 4123 49921.76 4700 53477.38 5890 53893.16 5555 54738.99 4734 55744.75 5110 56977.80 4023 74776.00 4666 85280.00 6011 94953.52 6004 110448.00 6021 111593.00 8001 117832.68 9001 146432.00 2053 <null> 4560 <null> 2051 <null> 21 rows processed