w3resource

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 Expression: Get the average salary for each post excluding programmer.

Relational Algebra Tree:

Relational Algebra Tree: Get the average salary for each post excluding programmer.

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.

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/postgresql-exercises/aggregate-function-and-groupby/aggregate-function-and-groupby-exercise-11.php