You can use aggregate functions to display information for groups of rows rather than for a whole table. For example, the president wants to know the average salary for employees assigned to each job rather than the average salary for all employees in the whole company. The POSITION table, where the salary information is maintained, has more than one row for each job. In order to retrieve the information for the president, you need to group all rows with the same job ID and then find the average salary for that group.
Using GROUP BY
To summarize information for groups of rows, such as all employees who have the same job ID, use the GROUP BY clause. The GROUP BY clause indicates which columns contain values to be grouped together.
How It's Done
To find the average salary for employees by job, use the GROUP BY clause and enter:
select job_id, avg(salary_amount)
from position
group by job_id;
The result looks like this:
JOB_ID (EXPR) ------ ------ 2051 <null> 2053 <null> 2077 23672.56 3333 23130.05 4012 37546.80 4023 74776.00 4025 43888.00 4123 49921.76 4130 45241.94 4560 <null> 4666 85280.00 4700 53477.38 4734 55744.75 5110 56977.80 5555 54738.99 5890 53893.16 6004 110448.00 6011 94953.52 6021 111593.00 8001 117832.68 9001 146432.00 21 rows processed
The GROUP BY clause grouped the rows of data by job and then AVG took the average salary for each group. For example, nine salaries are averaged for JOB_ID 3333. Three jobs have null for an average salary because they have only hourly employees.
|
Copyright © 2014 CA.
All rights reserved.
|
|