按列数除以整列

i tried to divide entire column (data) by the number of column(5) and insert it into another column (data average) :

data : 20 12 32 12 32

data_average : 20/5 12/5 32/5 12/5 32/5

I can use query :

update data set data_average = data / 5;

It works but I know that when I add more data to data column, it won' be /5 any more.

So i try to make it dynamically using query :

select count(*) as count from table

but I cant make it to the first query like

update data set data_average = data / count;

How can I make it divide by count?

MySQL has an AVG() function to do this work for you. So your query can do all the work, like this:

SELECT AVG( column_name ) AS average FROM table_name;

For reference: https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_avg

If you'd rather do this with CodeIgniter/PHP, then this is possible:

<?php

$query = $this->db->query('SELECT * FROM tablename');

$results = $query->result();

$rowCount = count( $results );

$total = 0;

foreach( $results as $row )
    $total += $row->column_name;

$average = $total / $rowCount;