LetsPlayYahtzee LetsPlayYahtzee - 24 days ago 7
MySQL Question

Add max of column to all rows of the column

I am trying to understand why I don't get a column when executing the following query. My goal is to make a simple sql query that will select a column and add the maximum value of the column to each row.

something like this

select rating + max(rating) from reviews;


But with this I get


this is incompatible with sql_mode=only full_group_by


If I change the
sql_mode
the query gets executed but instead of a column
I get only one value. Which in fact is the value of the first row of ratings summed with the maximum value.

If instead of the
max(rating)
part I use just a value like

select rating + 4 from reviews;


it works.

vkp vkp
Answer

You can cross join the max rating (which is one row) to the original table and add it to the rating column.

select r.rating + m.maxrating 
from reviews r
cross join (select max(rating) maxrating from reviews) m

or more simply

select rating + (select max(rating) from reviews)
from reviews