There is a table: enter image description here

It is necessary to calculate the average score for each product, even if the value is NULL, then it should not be considered when calculating the average score (). The second day I try to figure out - it's not that.

  • Where exactly are you having trouble? Show what you got. - 0xdb
  • the average value is calculated by the AVG() function, and add grouping by product (group by). - Mike
  • SELECT id_product, AVG (functionality_rate + quality_rate + design_rate) FROM ps_product_rating_users GROUP BY id_product - Mr.D1rk
  • Result of the request: joxi.ru/J2byPwxC4G35ym.jpg - Mr.D1rk
  • one
    then ask a question humanly. Give the input data with text, rather than with a picture and better directly in the form create table / insert that you can make a test database and try on it. And most importantly, on the basis of these data, give the result calculated by hands (the same as text) that you want to get. What would be clear what the "average rating" is. link "edit" under the text of the question. - Mike

1 answer 1

Line average:

 select id_product, (coalesce(functionality_rate,0)+coalesce(quality_rate,0)+coalesce(design_rate,0))/ (coalesce(functionality_rate/functionality_rate,0)+coalesce(quality_rate/quality_rate,0)+coalesce(design_rate/design_rate,0)) avg_ from t;

Product average:

 select id_product, avg(avg_) from (select id_product, (coalesce(functionality_rate,0)+coalesce(quality_rate,0)+coalesce(design_rate,0))/ (coalesce(functionality_rate/functionality_rate,0)+coalesce(quality_rate/quality_rate,0)+coalesce(design_rate/design_rate,0)) avg_ from t ) X group by id_product;