PHP script that can calculate average of columns in MySQL database

Question

I need some help on how to code my php script so that it will find the average of six columns in mysql database and store the new results in a new column inside the mysql database.

For example

the MSQL Database has the following columns

 Name    EXAM1       EXAM2     EXAM3    EXAM4    EXAM5

Then the following records are entered into the dataabse

 Name    EXAM1       EXAM2     EXAM3    EXAM4    EXAM5


Eric     10          20        10        20       10

Then the PHP script will generate this result

 Name    EXAM1       EXAM2     EXAM3    EXAM4    EXAM5     AVERAGE


Eric     10          20        10        20       10         14

So that in the MYSQL database, the records will show just like the PHP script results.

Any help?

Thanks.

Revise your design. You should have three columns: user_id, exam_id, score — Strawberry
– Strawberry, Commented Feb 5, 2017 at 8:35
I agree with @Strawberry. Also, you don't need PHP to calculate averages. See here: stackoverflow.com/questions/14787561/… — Stuart Wagner
– Stuart Wagner, Commented Feb 5, 2017 at 8:38

Bernd Buffen · Accepted Answer · 2017-02-05 09:14:41Z

1

You can do it direct in SQL like

UPDATE yourTable set AVERAGE = (EXAM1+EXAM2+EXAM3+EXAM4+EXAM5)/5;

Or you automatic generate the AVERAGE if you define your table like this:

CREATE TABLE yourTable (
  title VARCHAR(32) DEFAULT NULL,
  exam1 DOUBLE,
  exam2 DOUBLE,
  exam3 DOUBLE,
  exam4 DOUBLE,
  exam5 DOUBLE,
  average DOUBLE GENERATED ALWAYS AS ((exam1 + exam2 + exam3 + exam4 + exam5) / 5 ) STORED
);

sample

mysql> CREATE TABLE yourTable (
    ->   title VARCHAR(32) DEFAULT NULL,
    ->   exam1 DOUBLE,
    ->   exam2 DOUBLE,
    ->   exam3 DOUBLE,
    ->   exam4 DOUBLE,
    ->   exam5 DOUBLE,
    ->   average DOUBLE GENERATED ALWAYS AS ((exam1 + exam2 + exam3 + exam4 + exam5) / 5 ) STORED
    -> );
Query OK, 0 rows affected (0,02 sec)

mysql> INSERT INTO yourTable (title, exam1, exam2, exam3, exam4, exam5)
    -> VALUES('test1',1,1,22,33,44),('Test2',333,24,11,45,97);
Query OK, 2 rows affected (0,00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM yourTable;
+-------+-------+-------+-------+-------+-------+---------+
| title | exam1 | exam2 | exam3 | exam4 | exam5 | average |
+-------+-------+-------+-------+-------+-------+---------+
| test1 |     1 |     1 |    22 |    33 |    44 |    20.2 |
| Test2 |   333 |    24 |    11 |    45 |    97 |     102 |
+-------+-------+-------+-------+-------+-------+---------+
2 rows in set (0,00 sec)

mysql>

answered Feb 5, 2017 at 9:14

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zolly Zollyzo Over a year ago

thanks for the help. However, I get this error when I tried to follow the code you gave above. == #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GENERATED ALWAYS AS ((exam1 + exam2 + exam3 + exam4 + exam5) / 5 ) STORED )' at line 8

Bernd Buffen Over a year ago

which version you have installed select version();

Collectives™ on Stack Overflow

PHP script that can calculate average of columns in MySQL database

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related