164 ) 3) Department wise -Highest Salary of Each

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3) Department wise -Highest Salary of Each 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Question: Write a query to display the highest salary of each department as department name and highest salary.

Query: 

 

SELECT d.deptname,
       Count(e.empid) total_emp_in_dept,
       Max(e.salary)  AS maxsalary,
       Sum(e.salary)  AS sum_of_salaries
FROM   emp e
       JOIN dept d
         ON e.deptid = d.deptid
GROUP  BY d.deptname; 

----------------------------------------

  

deptname,	total_emp_in_dept,	maxsalary,	sum_of_salaries
'Human	Resources',	'2',	'62000.00',
'Engineering',	'6',	'120000.00',	'532000.00'
'Sales',	'4',	'58000.00',	'215000.00'
'Marketing',	'4',	'72000.00',	'275000.00'
'Finance',	'2',	'96000.00',	'186000.00'
'Customer	Support',	'2',	'52000.00',
'Research	&	Development',	'5',
'Legal',	'2',	'70000.00',	'138000.00'
'Operations',	'2',	'78000.00',	'154000.00'
'Administration',	'1',	'54000.00',	'54000.00'