SQL Average height of building-CodePudding

I have a database with buildings, with their names and heights. I want a query that gives me the names of the buildigs that differ less than 100 meters height from the average of all building.

I have tried:

SELECT Name FROM building 
WHERE Height BETWEEN ABS((AVG(Height)) - 100) AND ABS(AVG(Height))

But it is not working, any ideas? :)

CodePudding user response：

avg needs to be used in select, not in where

See How to use avg with where condition in the sql?

So, basically;

SELECT Name FROM mountain 
WHERE Height BETWEEN ( select ABS((AVG(Height)) from mountain) - 100) AND (select ABS(AVG(Height)) from mountain)

CodePudding user response：

Have a subquery to get the average mountain height:

SELECT Name FROM mountain 
WHERE (select AVG(Height) from mountain) BETWEEN Height - 100 and Height   100

CodePudding user response：

You may try using window function as the following:

SELECT name, height
FROM
(
  SELECT *,
   AVG(height) OVER () av
  FROM table_name
) T
WHERE ABS(height-av) <= 100

See a demo on SQL Server.

CodePudding user response：

If you want to keep your ABS idea, you can do this:

SELECT name FROM mountain 
WHERE ABS((SELECT AVG(Height) FROM mountain) - HEIGHT) < 100;

You just need to put in a subquery.