How do you use the AVG api in sql to manage grouped output?
-
How do you use the AVG api in sql to manage grouped output? For example: Given a SQL Table “Employee” Name Salary City John Doe 15000 Seattle Jane Doe 30000 Redmond Tim Wayne 25000 Seattle Write a SQL query that would return the average salary per city in the format shown below: City Salary Seattle 20000 Redmond 30000
-
How do you use the AVG api in sql to manage grouped output? For example: Given a SQL Table “Employee” Name Salary City John Doe 15000 Seattle Jane Doe 30000 Redmond Tim Wayne 25000 Seattle Write a SQL query that would return the average salary per city in the format shown below: City Salary Seattle 20000 Redmond 30000
Xarzu wrote:
Write a SQL query that would return the average salary per city in the format shown below:
For some reason this sounds like homework. What have you done so far for a query?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
-
How do you use the AVG api in sql to manage grouped output? For example: Given a SQL Table “Employee” Name Salary City John Doe 15000 Seattle Jane Doe 30000 Redmond Tim Wayne 25000 Seattle Write a SQL query that would return the average salary per city in the format shown below: City Salary Seattle 20000 Redmond 30000
-
Xarzu wrote:
Write a SQL query that would return the average salary per city in the format shown below:
For some reason this sounds like homework. What have you done so far for a query?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
-
Select City, AVG(Salary) from Employee where ... I don't know the rest Where city == city or something like that?
You could try:
select city, avg(salary) from Employee group by city
This will give you the average grouped by cities. How to sort the cities in the order you want to order by in your expected result, I'm leaving that as an exercise :-D
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
-
How do you use the AVG api in sql to manage grouped output? For example: Given a SQL Table “Employee” Name Salary City John Doe 15000 Seattle Jane Doe 30000 Redmond Tim Wayne 25000 Seattle Write a SQL query that would return the average salary per city in the format shown below: City Salary Seattle 20000 Redmond 30000
All Version of Sql server
Select City, AVG(Salary) AS Salary
FROM @t
Group By City
Order By City DESCThis can also be done by treating AVG function as Analytical function which is available since SQL Server 2005
Select City,Salary From
(
Select
Rn = Row_Number() Over(PARTITION BY City Order By (Select 1))
,City
,AVG(Salary) OVER(PARTITION BY City) AS Salary
From @t) X
Where X.Rn = 1
Order By City DESCThe answer for both the cases City Salary Seattle 20000 Redmond 30000
Niladri Biswas (Code Project MVP 2012)