Search code examples
sqlaverage

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

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.

Solution

  • 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