CODEWITHSUNDEEP
mysqldatabaseselectaverage
darsh
darsh

Reputation: 751

MySQL issue in getting average of a column

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.

Upvotes: 2

Views: 657

Answers (3)

Vallabh Patade
Vallabh Patade

Reputation: 5110

During first query you are asking the avg height and it's printing average height against the first record(Harry). In second query you are asking for avg of heights of all students in a group who are grouped on their names. Now in your case you have four groups, each containing only one record, as there is no duplicate name, so average height of one record will come as height of that record itself.

What you want to do is to print each record and against it, print avg height.

SELECT name, height, (SELECT AVG(height) FROM mytable) AS average_height 
FROM mytable;

Upvotes: 2

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT name, height, average_height 
FROM mytable, (SELECT AVG(height) average_height FROM mytable) A

OR

SELECT name, height, (SELECT AVG(height) FROM mytable) AS average_height 
FROM mytable

Upvotes: 2

user1919238
user1919238

Reputation:

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).

Upvotes: 5

Related Questions