How to get AVG of a column only if the all the row values for this specific column are not equal -1 ?

I have a table with a column named quarter, Its value can be 0-100. But in case when this quarter data was never initiated, I keep this column value to -1.

I need to get the average of this column data. But if all the rows value for this column value is -1, then the average should remain -1, otherwise just get the average of column quarter with non -1 row values and excluding the rows with some -1 values.

Note : if not all the rows value is -1, meaning some rows with -1 value and some with greater than 0 then -1 rows will be ignored to get the respective average value of this column.

I have tried something like this for now :

 SELECT
  sum(case when QARTER = -1 then 0 else Q1 end) as QUARTER_VALUE
 FROM TABLE yearData

UPDATE :

1. I get result somethings like this :

   

2. I want to get average of the column when all the values are not -1, but if there are -1 values with non -1 values, then ignore all the rows with -1 values to get the average.

3. If all the column values are -1 then keep the average as -1

Please guide me and help. Thank you :)

CodePudding user response：

Original question (treat -1 as 0 when averaging, unless all rows are -1):

case
    when max(quarter)=-1 then -1
    else avg(greatest(quarter,0))
end

If all rows have -1, then their max is -1 and it returns -1. Otherwise it takes the average, but using 0 for rows with a value of -1.

Revised question (skip -1 when averaging, but still return -1 if all rows are -1):

coalesce(avg(nullif(quarter,-1)),-1)

Treat -1 as null when averaging; averaging skips null values. If the average is null, all rows were -1 so use -1.

CodePudding user response：

Update your try slightly:

SELECT
 COALESCE(Avg(case when QUARTER <> -1 then QUARTER end), -1) as QUARTER_VALUE
FROM TABLE yearData

Avg function will not include null values, so case function turns -1 to null first, then average it all, then check fix null result to be -1 with coalesce.

CodePudding user response：

Try this solution :

SELECT
 case  when max(QUARTER) = -1 then -1   else avg(case when QUARTER = -1 then null else QUARTER end) end as result_col
from yourTable

this will do the following things :

1. Will keep the average to -1 if all the values are -1 for the qwuarter columns
2. will ignore the rows with value -1 to get the average in case there are some non -1 values too (because null values are not included in the average result, so they are skipped)

Hope it works for you! (Also credit goes to JonasMetzler and ysth for good answers near to your problem solution)