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
Postgres Query to Check a String Is a Number
Iif Statement With Multiple Conditions
Oracle Sql: Extracting Text Between Two Characters
Inserting Date Value into Date Field Using Laravel
Constraint for Phone Number in SQL Server
Postgresql Error: Fatal: Role "Username" Does Not Exist
How to Extract Part of a String in Hive
Find Out Where MySQL Is Installed on MAC Os X
Sequelize Connect Etimeout When Connecting to Remote MySQL Db
Datediff Getting the Previous Month
Auto Increment the Column Value by 1 Conditionally in Select Query in SQL
Find Records from One Table Which Don't Exist in Another
How to Count Number of Digits After a Decimal Place
Using Node.Js (Express) and MySQL to Insert for a Timestamp
Node.Js Mssql Tedius Connectionerror: Failed to Connect to Localhost:1433 - Connect Econnrefused