CODEWITHSUNDEEP
sqlaverage
user3676186
user3676186

Reputation: 29

SQL AVG cant get to work

I am having trouble displaying the average age alongside other data, i know it should display more then one field but it only displays one field. 

SELECT EmpName, Age,AVG(Age)
FROM   Employee
WHERE  Age > ( SELECT AVG(Age)
               FROM   Employee
             )

Upvotes: 0

Views: 360

Answers (3)

redsoxlost
redsoxlost

Reputation: 1235

You can use HAVING clause to achieve it 

select name,age,avg(age)
from emp
group by name,age
having age > (select avg(age) from emp)

Upvotes: 1

Muhab
Muhab

Reputation: 312

SELECT EmpName, Age, (SELECT AVG(Age) FROM Employee) AverageAge
FROM Employee
WHERE Age > (SELECT AVG(Age) FROM Employee);

here is the right code , i think u can't use AVG(Age) next to SELECT cause it's not an exist column.

Upvotes: 2

Patrick Hofman
Patrick Hofman

Reputation: 156938

You cannot get the line and get the overall average. Try to join them:

SELECT e.EmpName
,      e.Age
,      a.AverageAge
FROM   Employee e
CROSS
JOIN   ( SELECT AVG(Age) AverageAge
         FROM   Employee
       ) a
WHERE  Age > AverageAge

Upvotes: 3

Related Questions