LetsPlayYahtzee LetsPlayYahtzee - 11 months ago 58
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
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
part I use just a value like

select rating + 4 from reviews;

it works.

vkp vkp
Answer Source

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