Calculating mean with group by based on another column in QGIS Field Calculator

Question

I have daily temperature data for several cities from January to December. I'd like to calculate the average temperature per month for each city in a new column.

I have a column "city", "day", "month" and "temperature". I've tried to do the following in the field calculator, but the result gives the same average temperature for all cities.

case
    when "CITY" = 'LONDON' then mean("TEMP", group_by:="MONTH")
    else ''
        end  
|| 
case
    when "CITY" = 'PARIS' then mean("TEMP", group_by:="MONTH")
    else ''
        end 

Also, I imagine it's possible to do this without having to call the name of each city. This would be great, as there are hundreds of them. How can I modify this code to get the desired result?

I am using QGIS 3.30.1

Answer 1

Use this expression:

array_mean (array_agg (temperature, group_by:=city || month))

Answer 2

Try the following expression:

mean(expression:="temp", group_by:="month" || "city")