Finding the lowest average

[ifoam]

Let's say I have a table with the following data:

Customer:
----------
name | city | salary
john| Boston | 14000
billy | Boston | 32000
sam | Boston | 12000
jj | Dallas | 6000
greg | Dallas | 8000
josh | LA | 1200
ally | LA | 600000
bill | LA | 12000
bob | LA | 9800

how can i list the city with the lowest average salary?

I need to find the average salary of each city(should be 3), then choose the lowest from those 3 averages.

Can anyone help me?

 

[ifoam]

Select city
From Customer
Where MIN(SELECT Avg(salary) FROM Customer GROUP BY city);

im thinking it is something like that but i get the error: "At most one record can be return by this subquery."

 

[raskew]

Hi -

This can actually be done in one query, but it's a lot easier with two:

This will give you the average for each city:

SELECT Table1.City, Avg(Table1.Salary) AS AvgOfSalary
FROM Table1
GROUP BY Table1.City
ORDER BY Avg(Table1.Salary);

This uses the first query (Query45 in this case) to return the city with the minimum average salary.

SELECT TOP 1 Query45.City, Query45.AvgOfSalary
FROM Query45;

HTH - Bob

 

[ifoam]

Thanks Bob!

But, I'm doing this as a homework assignment and im pretty sure our professor wants it done in one query.

do you have any information on how to join those two together?

 

[ifoam]

SELECT TOP 1, city
FROM
(SELECT City, Avg(Salary) AS AvgOfSalary
FROM Customer
GROUP BY City
ORDER BY AVG(Salary) DESC);

that seems to be working. anyone see any problems with it?

 

[raskew]

Hi -

Try this, substituting your table name for Table1:

SELECT
    TOP 1 a.City
  , Avg(Table1.Salary) AS AvgOfSalary
FROM
   Table1 AS a 
INNER JOIN
   Table1 
ON
   a.City = Table1.City
GROUP BY
   a.City
ORDER BY
   Avg(Table1.Salary);

HTH - Bob

 

[EMP]

SELECT TOP 1, city
FROM
(SELECT City, Avg(Salary) AS AvgOfSalary
FROM Customer
GROUP BY City
ORDER BY AVG(Salary) DESC);

that seems to be working. anyone see any problems with it?

I can see two problems:-
1. Access doesn't like 'selecting from a select statement'. Once the query is saved, Access will change some brackets into square brackets and add some extras.

2. The DESC returns the city with the highest instead of the lowest average.


The simplest statement is:-

SELECT TOP 1 City, Avg(Salary) AS AvgOfSalary
FROM Customer
GROUP BY City
ORDER BY Avg(Salary);

The showing of average salary in the returned results is optional and can be removed. It doesn't affect the statement.

^