MySQL issue in getting average of a column

Question

I have a MySQL table having three columns id, name and height.

I want to run a query to return the name and height of all entries, along with the average height of all entries. All these things have to be done in a single query.

This is what I tried.

SELECT name, height, avg(height) as average_height FROM mytable

This query returns only a single row, but the average height is correct.

name    height    average_height
--------------------------------
Harry   165       169.5

then I tried this query

SELECT name, height, avg(height) as average_height FROM mytable GROUP BY name

then it returns all the rows, but the average is not correct

name    height    average_height
--------------------------------
Harry   165       165
Tom     170       170
George  180       180
Raj     163       163

How can I get the average as well as all the rows in a single query.

Answer 1

Here is one way:

SELECT name, height, average_height FROM mytable CROSS JOIN (
   select avg(height) as average_height FROM mytable
) as avg;

SQLFiddle example (Updated example to reflect suggestion to use CROSS JOIN).