Averaging specific rows

[jamie_kerwick]

I have an averaging query that works OK. However, i want to restict the rows that it averages.

The first query just averages ALL rows, and works fine, returning zero values where neccessary.

$count_sql = "SELECT avg( enquiries.acknowledgement_date - enquiries.date_received ) / 86400 as average, addressees.addressee FROM enquiries RIGHT JOIN addressees ON addressees.id = enquiries.addressee_id WHERE addressees.id = $addressee_row[addressee_id] GROUP BY addressee ORDER BY addressee ASC";

This second query is the restricted averaging query, however, if the average is zero it does NOT return a row.

$count_sql = "SELECT avg( enquiries.acknowledgement_date - enquiries.date_received ) / 86400 as average, addressees.addressee FROM enquiries RIGHT JOIN addressees ON addressees.id = enquiries.addressee_id WHERE addressees.id = $addressee_row[addressee_id] AND enquiries.enquiry_type != 2 GROUP BY addressee ORDER BY addressee ASC";

How do i need to modify the 2nd query so that if there is a zero average (ie no matching records to average) it returns the zero average, like the 1st query will do (currently it just doesn't return a row).

[sfhc]

Rather than modify the query you can do this:

<?php 
        $result=mysql_query($count_sql); 
        if(mysql_num_rows($result)==0){ 
              $avg=0; 
        } 
?>

[jamie_kerwick]

Thanks that works great. I've been getting that tied up in the sql i never thought of a PHP method

[sfhc]

I am sure that happens to all of us,
the trick sometimes is balancing sql and php functions ( wich thank god are beautifully suited to sql )