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

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

(

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

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

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

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

select AverageCol1234 =

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

AverageCol1234

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

3.0000

2.3333

-2.3333

1.0000

.5000

NULL

--= 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