Solved SQL query to get sum of statistics in different tables

[whitevo]

I found similar questions, but all of them are unanswered and made like year+ ago, so I make new one

my function:

function onUse(player, item, frompos, itemEx, topos)
local itemID = item:getId()
local itemAID = item:getActionId()

local highscoreData = db.storeQuery("SELECT attempts, kills, deaths FROM highscores WHERE monster = 'dummy' GROUP BY monster")
local attempts = result.getNumber(highscoreData, "attempts")
local kills = result.getNumber(highscoreData, "kills")

print(attempts)
print(kills)
end

prints:

1
0

Problem is, I want to get the total of all the attempts made against dummy and kills and deaths.
I clearly have more attempts than 1. Why it only takes the first result it sees?

Spoiler: database picture

 

[Printer]

Make a while loop and loop through the values.

 

[Ninja]

Use SUM(columnName).

local attempts, kills
local highscoreData = db.storeQuery("SELECT SUM(attempts) AS attempts, SUM(kills) AS kills, SUM(deaths) AS deaths FROM highscores WHERE monster = 'dummy' GROUP BY monster")
if highscoreData ~= false then
    attempts = result.getNumber(highscoreData, "attempts")
    kills = result.getNumber(highscoreData, "kills")
    result.free(highscoreData)
end

print(attempts)
print(kills)

 

[whitevo]

Use SUM(columnName).

local attempts, kills
local highscoreData = db.storeQuery("SELECT SUM(attempts) AS attempts, SUM(kills) AS kills, SUM(deaths) AS deaths FROM highscores WHERE monster = 'dummy' GROUP BY monster")
if highscoreData ~= false then
    attempts = result.getNumber(highscoreData, "attempts")
    kills = result.getNumber(highscoreData, "kills")
    result.free(highscoreData)
end

print(attempts)
print(kills)

yeah, just found the answer too in google xD and tested it. and even counted it xD just to be sure. The group by seems to be unnecessary.

But now second question. How to reset some fields?
Because I didn't think bosses what I summon myself and remove myself will end up in highscores, but apparently my trollings had consequences.

What I want to do is:
if attempts < deaths+kills then attempts = 0, deaths=0, kills=0 end (but as QUERY)

BTW:
result.free(highscoreData)
What is that line for?

and what is the difference between:
if highscoreData then
if highscoreData ~= false then

EDIT:
Clearly does not work xD (using select right now to see what rows it would change)

SELECT `kills`, `deaths`, `attempts`, `monster` FROM `highscores` WHERE `attempts` < `deaths`+`attempts`

EDIT2:
OK i see the fail in my WHERE now.. trol..
Ok, problems, solved.

 

[whitevo]

Make a while loop and loop through the values.

How do I loop trough values? Trying to order the statistics into ranks now.

 

[Codex NG]

These links will help you understand both languages and how they work with one another.
http://www.w3schools.com/php/
http://www.w3schools.com/sql/

 

[whitevo]

http://www.w3schools.com/sql/

This is not that helpful and you know it too..

But don't worry, Cornex helped me out with the loop question.

 

[Codex NG]

This is not that helpful and you know it too..

How is the link not helpful?
You don't know the language, I am providing you with a source to learn the language.

 

[whitevo]

How is the link not helpful?
You don't know the language, I am providing you with a source to learn the language.

*sigh*