AVERAGE AVERAGE - 1 month ago 8
MySQL Question

How to display AVG(AGE) using subquery?

I have to display Mothers_name, AGE and Average age from the table below using a subquery?

I tried

SELECT Mothers_name,
Age,
AVG(Age)
FROM MOTHERS
WHERE Age > (SELECT AVG(Age)
FROM MOTHERS)


but this doesn't display Average age.

+-----------+--------------+-----+------+
| MothersID | Mothers_name | Age | KIDS |
+-----------+--------------+-----+------+
| 41 | Kirke | 19 | 1 |
| 44 | Kiisu | 19 | 1 |
| 26 | Mormela | 20 | 2 |
| 45 | Merka | 20 | 1 |
| 19 | Zandra | 20 | 1 |
| 24 | Kiisuke | 20 | 1 |
| 18 | Kristlin | 21 | 1 |
| 1 | Imbi | 21 | 1 |
| 10 | Kiki | 22 | 1 |
| 8 | Lilleke | 22 | 3 |
| 29 | Silja | 22 | 1 |
| 17 | Plikake | 23 | 1 |
| 36 | Fly | 24 | 2 |
| 37 | Blanka | 24 | 1 |
| 16 | Moonika | 24 | 1 |
| 20 | Pisikeingel | 24 | 1 |
| 27 | Krista | 24 | 2 |
| 32 | Margariita | 24 | 2 |
| 13 | Signeke | 25 | 1 |
| 40 | Mari | 25 | 1 |
| 47 | Maiki | 26 | 1 |
| 31 | Liisa-Lota | 26 | 1 |
| 2 | Rapunzel | 26 | 2 |
| 46 | Pille | 26 | 1 |
| 14 | Eveke | 26 | 2 |
| 21 | Jessika | 27 | 1 |
| 48 | Lilleke | 27 | 2 |
| 15 | Nadja | 27 | 2 |
| 23 | Triinu | 27 | 2 |
| 33 | Annika | 27 | 1 |
| 35 | Nella | 28 | 2 |
| 6 | Miili | 28 | 1 |
| 34 | Piibe | 28 | 2 |
| 11 | Katriin | 28 | 2 |
| 5 | Kurr | 28 | 1 |
| 22 | Kriste | 28 | 3 |
| 4 | Kristall | 29 | 1 |
| 38 | Roosi | 29 | 2 |
| 39 | Ave | 29 | 3 |
| 50 | Katrin | 29 | 2 |
| 12 | Zipsik | 29 | 1 |
| 43 | Tups | 30 | 1 |
| 3 | Katariina | 30 | 1 |
| 28 | Carmen | 30 | 2 |
| 49 | Viki | 30 | 2 |
| 30 | Viivi | 31 | 1 |
| 7 | Patsike | 31 | 3 |
| 42 | Marja | 32 | 1 |
| 9 | Margareth | 32 | 2 |
| 25 | Ade | 34 | 2 |
+-----------+--------------+-----+------+

Answer

Try something like

SELECT * FROM (SELECT Mothers_name, Age,
   (SELECT AVG(Age) FROM MOTHERS) as total_avg
FROM MOTHERS) as tmp WHERE age > total_avg
Comments