Pedro del Sol Pedro del Sol - 3 months ago 13
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
SELECT * FROM feedback NATURAL JOIN (
  SELECT AVG(rating), MAX(feedback_date) feedback_date FROM feedback
) t

See it on sqlfiddle.

Comments