how to get a average of values except negative numbers in postgres

user_2753792 0
Viewed 221

I have a table which has has a column that i need to take the average but in my average i want to exclude minues numbers

im sure this im doing is wrong,but how can i do it?

     SELECT power_curve_quality where m_turbine_id='192.168.30.82'
     and m_date>='2020-08-01' and m_date<'2020-09-01' 
     FROM wh.t_statistics_daily_ext 
     EXCEPT select power_curve_quality<0 
     FROM wh.t_statistics_daily_ext 
    where m_turbine_id='192.168.30.82' and m_date>='2020-08-01' and m_date<'2020-09-01'
postgresql
3 Answers

If you want the negative observations in the divisor, then this will do it:

select avg(greatest(power_curve_quality, 0))
  from t_statistics_daily_ext
 where . . .

If you want to exclude negative observations altogether, then use this:

select avg(power_curve_quality)
  from t_statistics_daily_ext
 where . . . 
   and power_curve_quality >= 0
user_138083190

Something like:

create table avg(id int, fld_1 int);
insert into avg values(1, 2), (2, -1), (3, 4), (4, -4), (5, 3);
select avg(fld_1) from avg where fld_1 >= 0;
user_70706130

Just add power_curve_quality >= 0 into the WHERE clause like so:

SELECT
    avg(power_curve_quality)
FROM
    t_statistics_daily_ext
WHERE
    power_curve_quality >= 0 
    and m_turbine_id = '192.168.30.82'
    and m_date >= '2020-08-01'
    and m_date < '2020-09-01' 
    ;
user_9676210
Related