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 -- This SQL query calculates the average salary for each job title, excluding 'IT_PROG' job title.

SELECT job_id, AVG(salary) -- Selects the job_id and calculates the average salary for each job title
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
WHERE job_id <> 'IT_PROG' -- Filters out rows where the job_id is 'IT_PROG'
GROUP BY job_id; -- Groups the results by job_id, so that the average salary is calculated for each unique job_id

Explanation:

• The SQL query calculates the average salary for each job title, excluding the 'IT_PROG' job title.
• The SELECT statement selects both the job_id and the average salary for each job title.
• The FROM clause specifies the table from which to retrieve the data, which is the employees table.
• The WHERE clause filters out rows where the job_id is 'IT_PROG', excluding employees with the job title 'IT_PROG' from the calculation.
• The GROUP BY clause groups the results by job_id, ensuring that the average salary is calculated for each unique job title.
• For each job title, the AVG() function calculates the average salary by computing the sum of salaries for that job title divided by the number of employees with that job title.
• The result set will contain one row for each unique job_id, along with the average salary for each job title.

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.