addition, arithmetic operators and mysql, php

siobhan

Hypothetically speaking, I have four tables for a learning application: testscores1, testscores2, testscores3 and totaltestscores.

Each of the testscoretables has a list of exams taken and the score for the exam, but no total score field.

1) I need to find a way to make a total scores field that would add up all the scores of all the exams in each table (the score will be cumulative no average necessary).

2) I need to find a way to get all of these total scores from each of the three testscoretables into the totaltestscore tables and to have an ultimate totaltestscore field that adds everything up.

Is there anyway to do this in the mysql db? (I will have tests taken on 21 days by 40 learners so it would be good to automate it as much as possible to reduce errors, etc.)

Or is it only possible in a php page? I'll be displaying all the scores on php pages.

Could some explain the best way to do this with basic code?

thanks
siobhan

yelvington

SELECT SUM(columnname) AS somelabel FROM tablename;

siobhan

thanks very much for your reply! i just need a bit more explanation?

is this is a query for mysql that i could use to add score totals into a database table?

for instance, if my testscore1 table has fields test1, test2, test 3... test 21, would i create a sql query like so:

SELECT SUM(test1,test2, test3) AS somelabel FROM tbl_testscore1;

then would i write an insert query? what does somelabel refer to?

thanks again
siobhan

nemonoman

Ideally, you would design your data differently:

Table

Test Scores

Test Number
Score
Student ID
Date.

Something like that.

Then you'd populate each row with the appropriate data.

As it is, with a table like this:

TestScores

test1
test2
test3

etc.

You are stuck adding many columns together

If that's your design, however,

You'd use

SELECT SUM(test1+test2+test3...

etc.

until you have all 21 columns with+ signs. Commas won't work; + signs will.

The SQL reserved word 'AS' means

'call this calculated field by this name.'

So if you say

$result=mysql_query("SELECT SUM(test1+test2) AS 'results'
FROM testtable");
$row=mysql_fetch_array($result);

You can refer to the value like this:

$row['results']

AS is optional; you could also say:
$result=mysql_query("SELECT SUM(test1+test2)
FROM testtable");
$row=mysql_fetch_array($result);

and simply refer to the returned value as $row[0];

NOTE THIS: Typically, you don't store/insert calculated values in a separate table. This can be a maintenance nightmare.

Example: you mismarked test 10 for student id 5....you'll have to update bot the test table and appropriate summary table.

Calculations done on the fly by MySQL are VERY fast, and generally its better to calculate on the fly rather than store.

It might be time to get a book on this stuff.

siobhan

Thank you very much--that was exceptionally helpful!

One additional question: is it possible to order the results by SUM, for example, in the case of a table that has the top ten learners ordered not by their name but by their score (the SUM calculation)?

siobhan

nemonoman

Based on your current table structure
something like

SELECT studentid, test1+test2... (up to test21) AS totalscore
FROM yourtable
ORDER BY totalscore DESC
LIMIT 10

ORDER BY means sort
DESC means highest to lowest
LIMIT means just return this many records

If you used more like my suggested table structure

SELECT Studentid, SUM(testscore) FROM nemotable
GROUP BY Studentid
ORDER BY SUM(testscore)
LIMIT 10

GROUP BY means use the group of records: in this SUM all those with the same Studentid

bubblenut

This is the shit I'm talking about here about people explaining their solutions well.
F*****g good post nemonoman, if only I could live up to my own preaching 😃
Bubble

siobhan

Thank you, thank you, thank you, thank you, thank you!!! This worked perfectly and I've revised the tables as you suggested.

You mentioned books. I have PHP and MySQL Web Development, which is good for an overview but hard to follow sometimes. When I ran into this problem I looked through books at the bookshop and couldn't find anything that explained what I needed to do as clearly as you have done. You might have a good suggestion though?

Thanks again, you have been most helpful!

siobhan