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.
What is the difficulty level of this exercise?
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics