Finding the Highest Average Salary in SQL

How to find maximum avg

Columns resulting from aggregate functions (e.g. avg) usually get arbitrary names. Just use an alias for it, and select on that:

select max(avg_salary)
from (select worker_id, avg(salary) AS avg_salary
from workers
group by worker_id) As maxSalary;

Finding the highest average salary of a department using joins in SQL?

Your syntax looks like SQL Server (the "TOP 1"). In that database, I would do something like this:

SELECT TOP (1) WITH TIES t1.empName, t2.salary, t2.department
FROM table1 t1 INNER JOIN
table2 t2
ON t1.empID = t2.empID
ORDER BY AVG(t2.salary) OVER (PARTITION BY t2.department) DESC;

A more generic solution:

SELECT empName, salary, department
FROM (SELECT t.*,
DENSE_RANK() OVER (ORDER BY avg_salary) as seqnum
FROM (SELECT t1.empName, t2.salary, t2.department,
AVG(t2.salary) OVER (PARTITION BY t2.department) as avg_salary
FROM table1 t1 INNER JOIN
table2 t2
ON t1.empID = t2.empID
) t
) t
WHERE seqnum = 1;

Finding average highest salary

1.First you have to calculate the average TotalSale for each SalesPerson using the AVG function.

SELECT SalesPerson, AVG(TotalSale) AS 'TotalSale' 
FROM Sales
GROUP BY SalesPerson

2.Find out the max value from the table generated by the above query.

SELECT MAX(avgSales.TotalSale)
FROM (SELECT AVG(TotalSale) AS 'TotalSale' FROM Sales GROUP BY SalesPerson) AS avgSales

How to select MAX from AVG?

In SQL Server, you can order the records and use TOP 1 to keep only the record that has the highest amount:

SELECT TOP 1 Customer_id, AVG(Amount) AS [Average amount paid]
FROM Payment
GROUP BY customer_id
ORDER BY [Average amount paid] DESC;

Note: for this query to make sense, you need a GROUP BY clause. Without it, it would just return one record, with the average of payments within the whole table.



Related Topics



Leave a reply



Submit