How to find average when some of the fields contain null value?

Probably a much more elegant solution that this, but here you go: -

--==Sample Data==--

DECLARE @TABLE AS TABLE (pkey INT IDENTITY, col1 INT, col2 INT, col3 INT, col4 INT)

INSERT INTO @TABLE (col1, col2, col3, col4)

SELECT 11, 12, 11, NULL

UNION ALL SELECT 24, 22, NULL, 33

UNION ALL SELECT 44, NULL, NULL, 44

UNION ALL SELECT 55, NULL, NULL, 55

--==Not Pretty - Solution==--

SELECT e.pkey,

(MAX(ISNULL(e.col1,0))+MAX(ISNULL(e.col2,0))+MAX(ISNULL(e.col3,0))+MAX(ISNULL(e.col4,0))) /

(SUM(ISNULL(a.col1,0))+SUM(ISNULL(b.col2,0))+SUM(ISNULL(c.col3,0))+SUM(ISNULL(d.col4,0)))

FROM (SELECT pkey, 1 AS col1

FROM @TABLE

WHERE col1 IS NOT NULL) a

LEFT OUTER JOIN (SELECT pkey, 1 AS col2

FROM @TABLE

WHERE col2 IS NOT NULL) b ON a.pkey = b.pkey

LEFT OUTER JOIN (SELECT pkey, 1 AS col3

FROM @TABLE

WHERE col3 IS NOT NULL) c ON a.pkey = c.pkey

LEFT OUTER JOIN (SELECT pkey, 1 AS col4

FROM @TABLE

WHERE col4 IS NOT NULL) d ON a.pkey = d.pkey

LEFT OUTER JOIN @TABLE e ON e.pkey = a.pkey

GROUP BY e.pkey

Two ways i could think of:

METHOD 1

DECLARE @Table Table

(

COL1 INT,

COL2 INT,

COL3 INT,

COL4 INT

)

INSERT INTO @Table VALUES

(11, 12, 11, NULL )

,(24, 22, NULL, 33 )

,(44, NULL, NULL, 44 )

,(55, NULL, NULL, 55 )

--=== Method 1 ( USING ISNULL AND CASE )

SELECT ( ISNULL(COL1 ,0 ) + ISNULL(COL2 ,0 ) + ISNULL(COL3 ,0 ) + ISNULL(COL4 ,0 ) )

/ (( CASE WHEN COL1 IS NOT NULL THEN 1 ELSE 0 END ) +

( CASE WHEN COL2 IS NOT NULL THEN 1 ELSE 0 END ) +

( CASE WHEN COL3 IS NOT NULL THEN 1 ELSE 0 END ) +

( CASE WHEN COL4 IS NOT NULL THEN 1 ELSE 0 END )

) AS AVG_COL

FROM @Table

METHOD 2

--==== Method 2 ( USING UNPIVOT )

SELECT AVG ( Vals ) AVG_COL

FROM (SELECT Row_Num = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) ,

COL1 , COL2 , COL3 , COL4

FROM @Table ) UNPIVOT_SOURCE

UNPIVOT

( Vals FOR Cols IN ( COL1 , COL2 , COL3 , COL4 )) UNPIVOT_HANDLER

GROUP BY

Row_Num

Thanks a lot for reply man, but it is returning average as 0 when i am using Sign function.

I think the method with the SIGN functions should read :

(SIGN(ISNULL(Col1,0))+SIGN(ISNULL(Col2,0))+SIGN(ISNULL(Col3,0))+SIGN(ISNULL(Col4,0)))

No its still giving 0, i mean when i changed the syntax and made it sign(isnull(col1,0)), it is still not calculating it 🙁

Have u tried the other solutions posted?

Check your data and then your code. The method works just fine.

CREATE TABLE #Table (COL1 INT, COL2 INT, COL3 INT, COL4 INT)

INSERT INTO #Table (COL1, COL2, COL3, COL4) VALUES (1, 1, 1, 1)

INSERT INTO #Table (COL1, COL2, COL3, COL4) VALUES (1, 1, 1, NULL)

INSERT INTO #Table (COL1, COL2, COL3, COL4) VALUES (1, NULL, 1, NULL)

INSERT INTO #Table (COL1, COL2, COL3, COL4) VALUES (NULL, NULL, NULL, NULL)

SELECT (ISNULL(SIGN(COL1),0)

+ISNULL(SIGN(COL2),0)

+ISNULL(SIGN(COL3),0)

+ISNULL(SIGN(COL4),0))

FROM #Table

Try casting the divisor as a decimal with the desired degree of precision. If both divisor and dividend are integer and the quotient is less than 1, the result will be integer 0.

I can validate that based on the sameple data that you provided in the original post Chris's method works as intended.

The averages, for each row is 11.33,26.33,44.00,55.00.

The solutions posted using sign will not work correctly if the value is negative.

Also, they need to account for the possibility that all four columns are null.

create table #table

(col1 numeric(10,4), col2 numeric(10,4), col3 numeric(10,4), col4 numeric(10,4))

go

insert into #table (col1, col2, col3, col4) values (3, 7, -2, 4)

insert into #table (col1, col2, col3, col4) values (4, -4, 7, null)

insert into #table (col1, col2, col3, col4) values (-4, 4, -7, null)

insert into #table (col1, col2, col3, col4) values (1, null, 1, null)

insert into #table (col1, col2, col3, col4) values (-1, null, 2, null)

insert into #table (col1, col2, col3, col4) values (null, null, null, null)

go

selectAverageCol1234 =

convert(numeric(10,4),round(

(isnull(col1,0.0000)+isnull(col2,0.0000)+

isnull(col3,0.0000)+isnull(col4,0.0000))

/

nullif(case when col1 is null then 0.0000 else 1.0000 end +

case when col2 is null then 0.0000 else 1.0000 end +

case when col3 is null then 0.0000 else 1.0000 end +

case when col4 is null then 0.0000 else 1.0000 end

, 0.0000),4))

from

#table

drop table #table

Results:

AverageCol1234

--------------

3.0000

2.3333

-2.3333

1.0000

.5000

NULL

I finally had time to work on this....and then once I finished I find Michael had posted a more complete solution...aaaggh!

Never mind, I will post mine because I have done it now!

--= CREATE SOME TEST DATA

IF OBJECT_ID('tempdb..#Table') IS NOT NULL

DROP TABLE #Table;

CREATE TABLE #Table (COL1 INT, COL2 INT, COL3 INT, COL4 INT)

INSERT INTO #Table (COL1, COL2, COL3, COL4) VALUES (11, 12, 11, NULL)

INSERT INTO #Table (COL1, COL2, COL3, COL4) VALUES (24, 22, NULL, 33)

INSERT INTO #Table (COL1, COL2, COL3, COL4) VALUES (44, NULL, NULL, 44)

INSERT INTO #Table (COL1, COL2, COL3, COL4) VALUES (55, NULL, NULL, 55)

INSERT INTO #Table (COL1, COL2, COL3, COL4) VALUES (0, 0, 0, 0)

INSERT INTO #Table (COL1, COL2, COL3, COL4) VALUES (NULL, NULL, NULL, NULL)

INSERT INTO #Table (COL1, COL2, COL3, COL4) VALUES (0, 0, 0, NULL)

-- The idea here is to count the NON-NULL columns by

-- converting NULLS to 0 and anything else to 1s.

-- This is achieved by first using ABS to ensure that

-- all values are positive, the using SIGN to convert

-- non-zero values to 1, leaving only ones, zeros and nulls.

-- The nulls are converted to 0 using ISNULL, then all

-- the resulting ones and zeros are added to get a column

-- count.

-- IF the column count is zero, the result is converted to a NULL

-- using NULLIF. The resulting value is the divisor.

--

-- Next any NULL values in the columns are converted to zero

-- and the results are added to get a row total.

-- Now we can divide the row total by the column count (the divisor).

-- If the result of this calculation is NULL - this means we

-- either had a row total of zero or ALL NULLS, so we use COALESCE

-- to test for any column to have a NON-NULL value -

-- which will be a zero - if we find one, then zero is the result

-- anything else is undefined - i.e. NULL

SELECT

ISNULL(

CONVERT(

DECIMAL(10, 2),

1.0 *

(

ISNULL( COL1, 0 ) + ISNULL( COL2, 0 ) + ISNULL( COL3, 0 ) + ISNULL( COL4, 0 )

)

/

NULLIF(

ISNULL(SIGN(ABS(COL1)),0) + ISNULL(SIGN(ABS(COL2)),0) + ISNULL(SIGN(ABS(COL3)),0) + ISNULL(SIGN(ABS(COL4)),0)

,0 )

) ,

COALESCE(COL1,COL2,COL3,COL4)

)

FROM #Table