Q:

mysql group by and average for non zero values

I have a table as below
carrier   duration   date
a         10         05-09-2013 
a         0          04-09-2013 
b         1          05-09-2013 
b         3          04-09-2013  
c         2          05-09-2013 
c         3          04-09-2013

i would like to calculate the avg based on the carrier and on the total duration
the result should  be:
carrier a = avg is 10
carrier b = avg is 2
carrier c = ave is 2.5

it should calculate the total duration for the non zero values only so for carrier A since it dint had any activity in 04-09-2013 date then the avg should be 10/1 i.e total duration/ number of dates with non- zero duration)

A:

select                  
  carrier,           -- carrier column in result set
  avg(duration)      -- average duration column in result set
from tablename       -- specify what table to get results from
where duration>0     -- condition excluding table rows where duration=0
group by carrier;    -- All rows with the same 'carrier' value are grouped 
                     -- together into one row in the result set.  This works with 
                     -- the aggregate function avg();

