MySQL SELECT SUM(Field) FROM TABLE

Databases
1

I am very new to MySQL and I am stuck on the next step. Say I have a table “EXAM” and there are 2 fields ("Subject and “Marks”).

EXAM

Subject   Marks
English   50
Maths    50
Science   50

I think the query to get the sum total of Marks (which is 150) is:
SELECT SUM(Marks) FROM EXAM

In Xojo, this will be:

Dim rs as RecordSet
rs = mDB.SQLSelect(“SELECT SUM(Marks) FROM EXAM”)

Where in rs do I get the return result back?

I can’t seem to figure which “member” or rs. to look for. In fact, when I type rs. and hit the tab key, I can’t find the appropriate place to find the result that is returned.

I appreciate any help.

2 
thesum = rs.idxfield(1).integervalue
3

Wow that works perfect! Thanks Jean-Yves.

Now I tried to see if there is a more efficient way to the final goal for the moment. I have 3 fields in the database that I want to get the sum back individually in 3 separate values.

Name  Col1   Col2   Col3
abc   50   60   70
def   50   60   70
ghk   50   60   70

The result will be 150, 180 and 210.

I have done a fair amount of googling before this and if I may remember there may have been an example like:
SELECT SUM(Col1) SUM(Col2) SUM(Col3) FROM EXAM

Is this possible and if yes where to get the return values?

4 
SELECT SUM(Col1) SUM(Col2) SUM(Col3) FROM EXAM



sumCol1 = rs.idxfield(1).integervalue
sumCol2= rs.idxfield(2).integervalue
sumCol3 = rs.idxfield(2).integervalue

I however STRONGLY suggest you use field NAMES not numbers

5

Just get the column values in order:

col1Sum = rs.IdxField(1).IntegerValue col2Sum = rs.IdxField(2).IntegerValue col3Sum = rs.IdxField(3).IntegerValue

You can also probably give the columns names by doing something like this:

SELECT SUM(Col1) As Col1Sum, SUM(Col2) As Col2Sum, SUM(Col3) As Col3Sum
And then refer to them by name:

col1Sum = rs.Field("Col1Sum").IntegerValue col2Sum = rs.Field("Col2Sum").IntegerValue col3Sum = rs.Field("Col3Sum").IntegerValue

6

Thanks everyone. This is super. Due to my lack of MySQL syntax, I have been trying to do it going into loops the last few hours until I got very lost in the maze. This is real easy now.

Thanks for all the help. I still have some way to go. It is already 00.57am here.