Lua More complicated database queries?

[Michael Orsino]

Hey guys,
Is it possible to have more complicated database queries?
Currently I can only get SELECT, FROM, WHERE and LIMIT to work

I am trying to create a function to return the average level of all players.
This is an easy database query: SELECT AVG (level) FROM players;

Is there a way to make this work in LUA?
this does not work: db.getResult("SELECT AVG `level` FROM `players`;")
the server seems to reads "AVG" as the column, and so everything goes to shit from there.

Thanks a lot,
Michael

EDIT: OTSYS_SQLITE3_PREPARE(): SQLITE ERROR: no such column: AVG (SELECT AVG "level" FROM "players"

 

[TGYoshi]

Run this query for example:

SELECT AVG(level) FROM players
=> The column will be called AVG(`level`) or something. Quite confusing.

Instead just run:

SELECT AVG(level) AS avglevel FROM players
=> Now it'll be called avglevel.

 

[Michael Orsino]

I think you may have misinterpreted what I am trying to achieve.
I want to create a function that returns the average level of all players.
I can get the result I want myself if i run the query directly (SELECT AVG (level) FROM players, but I cannot make the below work as a function in LUA.

function getAverageLevel()
db.getResult("SELECT AVG `level` FROM `players`;")

Any idea how to make the above query work in a function, or how to achieve the function otherwise?

 

[Tibiamakers]

you can do, select count * for select the number of players you got, later make de same with WHERE vocation = 1 and when you got the 2 numbers in variables just make an operation

number of players with vocation 1 / total players

and I think you will get the average of knights or players with vocation 1 of your server, good luck


edit: later you have to multiply the result by 100 to get the percentatge of players with vocation 1, because you will get a decimal number