SQL SORTING and FILTERING on HR Database: Display job ID, number of employees, sum of salary, and difference between highest and lowest salary for a job

SQL SORTING and FILTERING on HR Database: Exercise-23 with Solution

23. From the following table, write a SQL query to count the number of employees, sum of all salary, and difference between the highest salary and lowest salary by each job id. Return job_id, count, sum, salary_difference.

Sample table: employees

Sample Solution:

SELECT job_id, COUNT(*), SUM(salary), 
	MAX(salary)-MIN(salary) AS salary_difference 
		FROM employees 
			GROUP BY job_id;

Sample Output:

   job_id   | count |    sum    | salary_difference
------------+-------+-----------+-------------------
 AC_ACCOUNT |     1 |   8300.00 |              0.00
 ST_MAN     |     5 |  36400.00 |           2400.00
 IT_PROG    |     5 |  28800.00 |           4800.00
 SA_MAN     |     5 |  61000.00 |           3500.00
 AD_PRES    |     1 |  24000.00 |              0.00
 AC_MGR     |     1 |  12000.00 |              0.00
 FI_MGR     |     1 |  12000.00 |              0.00
 AD_ASST    |     1 |   4400.00 |              0.00
 MK_MAN     |     1 |  13000.00 |              0.00
 PU_CLERK   |     5 |  13900.00 |            600.00
 HR_REP     |     1 |   6500.00 |              0.00
 PR_REP     |     1 |  10000.00 |              0.00
 FI_ACCOUNT |     5 |  39600.00 |           2100.00
 SH_CLERK   |    20 |  64300.00 |           1700.00
 AD_VP      |     2 |  34000.00 |              0.00
 SA_REP     |    30 | 250500.00 |           5400.00
 ST_CLERK   |    20 |  55700.00 |           1500.00
 MK_REP     |     1 |   6000.00 |              0.00
 PU_MAN     |     1 |  11000.00 |              0.00
(19 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find those employees who worked more than two jobs in the past. Return employee id.
Next: From the following table, write a SQL query to find each job ids where two or more employees worked for more than 300 days. Return job id.