#[Sqlite] Score calculation

13 messages · Page 1 of 1 (latest)

pallid meteor

I have a table for games such games(id int primary key, name text) and a table for reviews such reviews(reviewerName text, gameId integer, gameplay integer, complexity integer, polishing integer, clarity integer, art integer, originality integer, feedback text, unique (reviewerName, gameid) on conflict fail, foreign key(gameid) references games(id)). Now I want to calculate a scores for each game by summing each column of the reviews table (like all the integer columns) and averaging them out and then adding all the average values each game got and then averaging them again to get a single score for each game. This part I can't wrap my head around :/

shrewd creekBOT
  • Consider reading #how-to-get-help to improve your question!
  • Explain what exactly your issue is.
  • Post the full error stack trace, not just the top part!
  • Show your code!
  • Issue solved? Press the button!
  • ✅ Marked as resolved by OP
pallid meteor
jolly beacon

Something like

SELECT gameId, AVG((gameplay+complexity+polishing+clarity+art+originality)/6) FROM reviews GROUP BY gameId
pallid meteor

I have been trying to make like 2 inner joins for this 😭

let me try and see

okay it works but now I need to change all the integer fields to real fields because it seems to lose some of the data in the process :/

thanks qjuh, you saved me a headache

jolly beacon

Ah, you probably need to divide by 6.0

Else it uses integer division and rounds

Then it should work with int fields

pallid meteor