How to show the average and use it like condition in the same query?-CodePudding

I am working with a data base and I need to show the people who are older than the age average in a city comparing their age against that average. My code shows the people who is older than the average....but I can't show the average of all the people (it's allways the same number) in each line.

SELECT name, age FROM people
    WHERE age > (SELECT AVG(age) FROM people);

I need to show something like this:

name     age     average
Mick      18        17.5
Sarah     25        17.5
Joe       38        17.5

Any help, please.

CodePudding user response：

You can write the same subquery to calculate the average age within select list:

SELECT name, age, (SELECT AVG(age) FROM people) average FROM people
    WHERE age > (SELECT AVG(age) FROM people);

Or if your database allows window function you can do this:

select name,age,average from
(
  SELECT name, age, AVG(age) over() average FROM people
)t where age>average