Average across columns - How can I do this better and in cleaner code?

Database
WHS
Fan

Average across columns - How can I do this better and in cleaner code?

I am tying to get average over 5 periods - periodic data stored in columns.

The output is mathematically correct.

How can I write the code better and in a cleaner manner

Code I used:

SEL

SEG_ID,
SEG_NAME,

INC_2014,
INC_2013,
INC_2012,
INC_2011,
INC_2010,

CASE WHEN INC_2014 IS NOT NULL THEN 1 ELSE 0 END AS l1,
CASE WHEN INC_2013 IS NOT NULL THEN 1 ELSE 0 END AS l2,
CASE WHEN INC_2012 IS NOT NULL THEN 1 ELSE 0 END AS l3,
CASE WHEN INC_2011 IS NOT NULL THEN 1 ELSE 0 END AS l4,
CASE WHEN INC_2010 IS NOT NULL THEN 1 ELSE 0 END AS l5,

(l1+l2+l3+l4+l5) AS INC_cnt,

(ZEROIFNULL(INC_2014) + ZEROIFNULL(INC_2013) + ZEROIFNULL(INC_2012) +
ZEROIFNULL(INC_2011) + ZEROIFNULL(INC_2010))/NULLIFZERO(INC_cnt) AS Avg_5Y_INC

FROM db.seg_inc

ORDER BY Avg_5Y_INC DESC;;;

Output I got:

SEG_ID SEG_NAME INC_2014 INC_2013 INC_2012 INC_2011 INC_2010 l1 l2 l3 l4 l5 INC_cnt  Avg_5Y_INC 
9346 WESF 6,776 6,408 6,270 5,914 5,530 1 1 1 1 1 5 6,180
1605 WOOH 5,749 5,622 5,362 5,133 4,911 1 1 1 1 1 5 5,355
8590 OLAM 0 253 229 132 83 0 1 1 1 1 4 174
3877 PRVE 0 0 77 81 79 0 0 1 1 1 3 79
7369 THHR 0 0 0 155 171 0 0 0 1 1 2 163
8804 BEGR 898 0 0 0 0 1 0 0 0 0 1 898

Thanks in advance, Will

BTW I tried to post earlier using code snippets like those posted by Dieter Noth but didn't succeed.

3 REPLIES
WHS
Fan

Re: Average across columns - How can I do this better and in cleaner code?

Any body? Do you think the code is correct?

Junior Contributor

Re: Average across columns - How can I do this better and in cleaner code?

Hi Will,

logic is correct, code is correct, result is correct :-)

I would just do some cosmetic changes by replacing the proprietary ZEROIFNULL/NULLIFZERO with a Standard SQL COALESCE/ NULLIF.

WHS
Fan

Re: Average across columns - How can I do this better and in cleaner code?

Thanks Dieter. Much appeciated. It means a lot coming from you

I will make the suggested change.

Rgds, Will