CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Gordon Copestake
Gordon Copestake

Reputation: 1647

Average across multiple columns with varying data

I have 10 decimal columns and I would like to add a computed column to my table that contains the average of these 10. A complication is that not every record has all 10 columns filled in. Some records have 4 some have 8 and some have 10.

e.g.

ID D1 D2 D3 D4 D5 D6 D7 D8 D9 D10
1  12 19 13 14
2  32 53 34 54 65 34 12 09
3  41 54 33 61 71 12 09 08 08 12

How can I get the average of these where ID1 = 14.5, ID2 = 36.625 etc

I can't just do D1 + D2 + D3... / 10 as the 10 isn't always 10

The ideal would just be to do AVG(D1:D10) but clearly the world isn't ideal!

Upvotes: 0

Views: 47

Answers (3)

Joe Taras
Joe Taras

Reputation: 15379

You can't use AVG aggregate function (because it works on rows) but you can calculate an average using the following query:

SELECT 
    (ISNULL(D1,0) + ISNULL(D2,0) + 
    ISNULL(D3,0) + ISNULL(D4,0) + ISNULL(D5,0) + 
    ISNULL(D6,0) + ISNULL(D7,0) + ISNULL(D8,0) + 
    ISNULL(D9,0) + ISNULL(D10,0)) /
    CASE 
    WHEN 
        D1 IS NOT NULL 
        OR D2 IS NOT NULL
        OR D3 IS NOT NULL
        OR D4 IS NOT NULL
        OR D5 IS NOT NULL
        OR D6 IS NOT NULL
        OR D7 IS NOT NULL
        OR D8 IS NOT NULL
        OR D9 IS NOT NULL
        OR D10 IS NOT NULL
    THEN
        (
        CASE
            WHEN D1 IS NOT NULL THEN 1 ELSE 0
        END +
        CASE
            WHEN D2 IS NOT NULL THEN 1 ELSE 0
        END +
        CASE
            WHEN D3 IS NOT NULL THEN 1 ELSE 0
        END +
        CASE
            WHEN D4 IS NOT NULL THEN 1 ELSE 0
        END +
        CASE
            WHEN D5 IS NOT NULL THEN 1 ELSE 0
        END +
        CASE
            WHEN D6 IS NOT NULL THEN 1 ELSE 0
        END +
        CASE
            WHEN D7 IS NOT NULL THEN 1 ELSE 0
        END +
        CASE
            WHEN D8 IS NOT NULL THEN 1 ELSE 0
        END +
        CASE
            WHEN D9 IS NOT NULL THEN 1 ELSE 0
        END +
        CASE
            WHEN D10 IS NOT NULL THEN 1 ELSE 0
        END
        )
   ELSE 1
   END
FROM yourtable

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93714

Use Values table valued constructor to unpivot the data then find average per ID. Try this

select id,avg(data) from Yourtable 
cross apply 
(values(D1), (D2), (D3), (D4), (D5), (D6) ,(D7), (D8), (D9) ,(D10)) cs (data)
group by id

Or if your want decimal values then use this.

select id,sum(data)/sum(case when data is not null then 1.0 else 0 end) from Yourtable 
cross apply 
(values(D1), (D2), (D3), (D4), (D5), (D6) ,(D7), (D8), (D9) ,(D10)) cs (data)
group by id

Upvotes: 1

jarlh
jarlh

Reputation: 44766

AVG for each id:

select id, avg(d) from
(
select id, id1 as d from tablename
union all
select id, id2 as d from tablename
union all
select id, id3 as d from tablename
union all
select id, id4 as d from tablename
union all
select id, id5 as d from tablename
union all
select id, id6 as d from tablename
union all
select id, id7 as d from tablename
union all
select id, id8 as d from tablename
union all
select id, id9 as d from tablename
union all
select id, id10 as d from tablename)
group by id

Upvotes: 1

Related Questions