CODEWITHSUNDEEP
sqlaverage
Juventino1897
Juventino1897

Reputation: 39

Compare a value to an average value SQL

I have a database with some persones stored in it, this persons have a residuallLave (holiday days they haven´t needed). Now I want to get the average value of this residualLeave and compare which persons have more than the average. But I get only one person back and this can't be true because there are more persons with a more residualLeave.

Code:

enter image description here

Output:

enter image description here

Upvotes: 0

Views: 420

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Just for fun, here is a MySQL 8+ version using analytic functions:

SELECT persid, firstname, lastname, residualleave
FROM (SELECT *, AVG(residualleave) OVER () AS avgresidualleave FROM tpersons) t
WHERE residualleave > avgresidualleave;

Upvotes: 0

juergen d
juergen d

Reputation: 204766

Your query won't work because you compare "normal" columns with aggregated ones without grouping. It is way simpler like this:

select *
from tpersons
where residualLeave > (select avg(residualLeave) from tpersons)

Upvotes: 2

Related Questions