CODEWITHSUNDEEP
postgresql
moris62
moris62

Reputation: 1045

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

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'

Upvotes: 2

Views: 524

Answers (3)

Timur Shtatland
Timur Shtatland

Reputation: 12377

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' 
    ;

Upvotes: 1

Adrian Klaver
Adrian Klaver

Reputation: 19664

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;

Upvotes: 1

Mike Organek
Mike Organek

Reputation: 12494

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

Upvotes: 2

Related Questions