CODEWITHSUNDEEP
sqlsql-serveraverage
Janek
Janek

Reputation: 85

Average without avg function

I need to calculate average value from 6 columns but when there is an empty field I don't want to have it calculated.

For example, if I have (2, 3, 2, 3, 2, 3) I should get 15/6 = 2,5 but when it is (2, 3, 2, 3, 'empty', 'empty') I need to have 10/4 = 2,5.

How to achieve this?

This is average from 6 columns co I can't use avg function which ignores NULLs as default.

Upvotes: 2

Views: 6043

Answers (5)

Rigel1121
Rigel1121

Reputation: 2016

Catch your NULL values by using CASE statement. See Below:

SELECT
     AVG(ISNULL(Column_to_Average,0)[Column_to_Average])
FROM #yourtable

OR

SELECT
     AVG(CASE WHEN Column_to_Average IS NULL OR Column_to_Average=''
              THEN 0 ELSE Column_to_Average END)
FROM #yourtable

OR You can also do this if you want to get the average of each rows.

SELECT
     (
     (CASE WHEN Column_to_Average_1 IS NULL OR Column_to_Average_1='' THEN 0 ELSE Column_to_Average_1 END) +
     (CASE WHEN Column_to_Average_2 IS NULL OR Column_to_Average_2='' THEN 0 ELSE Column_to_Average_2 END) +
     (CASE WHEN Column_to_Average_3 IS NULL OR Column_to_Average_3='' THEN 0 ELSE Column_to_Average_3 END) +
     (CASE WHEN Column_to_Average_4 IS NULL OR Column_to_Average_4='' THEN 0 ELSE Column_to_Average_4 END) +
     (CASE WHEN Column_to_Average_5 IS NULL OR Column_to_Average_5='' THEN 0 ELSE Column_to_Average_5 END) +
     (CASE WHEN Column_to_Average_6 IS NULL OR Column_to_Average_6='' THEN 0 ELSE Column_to_Average_6 END)
     )/6 Column_to_Average
FROM #YourTable

Upvotes: 1

Guffa
Guffa

Reputation: 700372

You face two problems:

  1. You have to convert the strings to numbers, and avoid the null and empty strings.

  2. You need to calculate the average of columns, so the avg function isn't usable as long as the data is in that form.

Basically there are two possible approaches, either you can just use the column values, or you can turn the columns into rows.

Using the column values naturally becomes repetetive. You have to both convert each column, and check how many usable columns there are:

select
  (
    case when Col1 is null or Col1 = '' then 0.0 else cast(Col1 as float) end +
    case when Col2 is null or Col2 = '' then 0.0 else cast(Col2 as float) end +
    case when Col3 is null or Col3 = '' then 0.0 else cast(Col3 as float) end +
    case when Col4 is null or Col4 = '' then 0.0 else cast(Col4 as float) end +
    case when Col5 is null or Col5 = '' then 0.0 else cast(Col5 as float) end +
    case when Col6 is null or Col6 = '' then 0.0 else cast(Col6 as float) end
  ) / (
    case when Col1 is null or Col1 = '' then 0.0 else 1.0 end +
    case when Col2 is null or Col2 = '' then 0.0 else 1.0 end +
    case when Col3 is null or Col3 = '' then 0.0 else 1.0 end +
    case when Col4 is null or Col4 = '' then 0.0 else 1.0 end +
    case when Col5 is null or Col5 = '' then 0.0 else 1.0 end +
    case when Col6 is null or Col6 = '' then 0.0 else 1.0 end
  ) as Average
from
  TheTable

You could create user defined function to do the conversion and counting, but you still need to do it for each column.

(A word of caution also; if there isn't a value in any of the columns, this would give you a division by zero error.)

You can use unpivot to turn the columns into rows, but you need a unique value to group the result on to keep the rows together that belong together. Example:

select
  Id, avg(cast(Col as float))
from
  TheTable
unpivot
  (Col for ColName in (Col1, Col2, Col3, Col4, Col5, Col6)) x
where
  Col <> '' and Col is not null
group by
  Id

Upvotes: 0

Dudi Konfino
Dudi Konfino

Reputation: 1136

select sun(x)  --sql server ignore null,
       count(x)  --sql server ignore null,
       sum(x)/count(x) as avgx
from tbl

Upvotes: 0

Mike Nakis
Mike Nakis

Reputation: 61993

You can create a view or subquery which skips rows with "empty" values and take an average on the view or subquery. 

SELECT AVG(value) FROM 
    (SELECT value FROM table WHERE value <> the_value_that_janek_considers_as_empty)

But I would seriously advise you to reconsider your notion of "empty". There is no such thing as an empty numeric value in databases. Either you say "empty" when in fact you mean "null", or you are doing something horrible, like storing numbers in textual columns.

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44326

Ignoring nulls is QUITE easy, sqlserver already ignores null values:

SELECT avg(x) 
FROM (values(4),(6),(null)) x(x)

Result

5

Edit since your comment says your numeric column is a varchar - which is silly, do this instead to handle empty and null values:

SELECT 
  avg(cast(nullif(x, '') as decimal(18,2))) 
FROM (values('4'),('6'),(null), ('')) x(x)

Upvotes: 1

Related Questions