PostgreSQL Aggregate Functions and Group By: Get the average salary for each post excluding programmer

11. Write a query to get the average salary for each post excluding programmer.

Sample Solution:

Code:

SELECT job_id, AVG(salary) 
FROM employees 
WHERE job_id <> 'IT_PROG' 
GROUP BY job_id;

Sample table: employees

Output:

pg_exercises=# SELECT job_id, AVG(salary)
pg_exercises-# FROM employees
pg_exercises-# WHERE job_id <> 'IT_PROG'
pg_exercises-# GROUP BY job_id;

   job_id   |          avg
------------+------------------------
 AC_ACCOUNT |  8300.0000000000000000
 ST_MAN		|	7280.000000000000
 SA_MAN     |     12200.000000000000
 AD_PRES    |     24000.000000000000
 AC_MGR     | 12000.0000000000000000
 FI_MGR     | 12000.0000000000000000
 AD_ASST    |  4400.0000000000000000
 MK_MAN     | 13000.0000000000000000
 PU_CLERK   |  2780.0000000000000000
 HR_REP     |  6500.0000000000000000
 PR_REP     | 10000.0000000000000000
 FI_ACCOUNT |  7920.0000000000000000
 SH_CLERK   |  3215.0000000000000000
 AD_VP      |     17000.000000000000
 SA_REP     |  8350.0000000000000000
 ST_CLERK   |  2785.0000000000000000
 MK_REP     |  6000.0000000000000000
 PU_MAN     | 11000.0000000000000000
(18 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query to get the department ID and the total salary payable in each department.
Next: Write a query to get the total salary, maximum, minimum and average salary of all posts for those departments which ID 90.