Pedro del Sol Pedro del Sol - 1 year ago 62
MySQL Question

Select AVG of a column and a single specific row

feedback table

-------------------------------
|rating|feedback|feedback_date|
-------------------------------
| 5 | good | 1452638788 |
| 1 | bad | 1452638900 |
| 0 | ugly | 1452750303 |
| 3 | ok | 1453903030 |
-------------------------------


desired result

average_rating | rating | feedback | feedback_date
2.25 | 3 | ok | 1453903030


Is it possible (in a single query) to select the average of one column and also one specific row from the table?

For example, i'd like to retrieve the average of the column
rating
and the most recent row as a whole.

I tried the following, and also with the
ORDER BY
direction as
DSC
but they both just gave me the
average_rating
and the first row in the table.

SELECT AVG(f.rating) AS average_rating, f.* FROM feedback f ORDER BY feedback_date ASC

Answer Source
SELECT * FROM feedback NATURAL JOIN (
  SELECT AVG(rating), MAX(feedback_date) feedback_date FROM feedback
) t

See it on sqlfiddle.