Sum if condition

celiahuang

Hi, I have a table in the database of historical records. Call it "table 1". Let's say it looks like this

---

Item Name     row     action     Score
Item1              1        create        1
Item2              2        create        2
Item3              3       create          5
Item1              1       edit              3

---

I want to calculate the average scores of item, i.e. no matter produced by 'create' or 'edit', I want to average the Score of the item with the same Row number.
for example, here: average for item 1 is (1+3)/2=2

---

I want to create a function to do this in php ==> because i will need to use it later in 'preEdit'

function average ($db, $id, $values, $row) {
// choose table in database
// if condition
if ($values['row' === ?] {
// then average all scores for those rows that have the same value under column 'row'
}
}

Also wondering how to insert the resulted average scores into cells in another table in preEdit.

thanks a lot!!!!

celiahuang

sorry if the table above looks awful.

also really need to know how to insert these values in another table in php

celiahuang

on( 'preEdit', function ( $editor, $values ) {

// use calculated value in Table 1 to insert to Table 2
// final results should be in Table 2

}

allan

Hi,

What you would need to do is query the database to get the value required. In this case using an SQL function to take the average might be appropriate. You can do that using the $db->sql() method that the Editor Database class provides.

The other option would be to query the database to get the values for the item, then you can calculate the average in PHP.

Allan

celiahuang

after I query the table,
$result = $db -> sql (" SELECT ... From ... ")
How to I set this value for a field in php?

for example,

// calculate average scores
->on( 'preEdit', function ( $editor, $id, $values ) {
    $result = $db -> sql (" SELECT ... From ... ")

    $editor
        ->field( 'Column A' )
        **->setValue( $result ); //here, this line does not work**
} )

allan

Is Column A in your list of fields?

Also, you would need to use $result->fetch()['columnName'] since $result is a Result instance.

Allan

celiahuang

Thank you.
it also depends on the data type!