cancel
Showing results for 
Search instead for 
Did you mean: 

select avg (ignore null)

powerpeanuts
New Contributor II

select avg[col1;col2] from t

would give average of col1 and col2 including null. What is the "ignoring null" version of it? Thanks.

1 ACCEPTED SOLUTION

davidcrossey
Moderator Moderator
Moderator

Hi powerpeanuts,

As per this note avg does ignore nulls for a single vector, so you could apply the average to each pair of values from each column as follows:

q)t:([]c1:`a`b`c;c2:1 0N 2;c3:5 3 5)
q)update c4:avg each flip (c2;c3) from t
c1 c2 c3 c4
------------
a  1  5  3
b     3  3
c  2  5  3.5

Hope this helps

View solution in original post

6 REPLIES 6

davidcrossey
Moderator Moderator
Moderator

Hi powerpeanuts,

As per this note avg does ignore nulls for a single vector, so you could apply the average to each pair of values from each column as follows:

q)t:([]c1:`a`b`c;c2:1 0N 2;c3:5 3 5)
q)update c4:avg each flip (c2;c3) from t
c1 c2 c3 c4
------------
a  1  5  3
b     3  3
c  2  5  3.5

Hope this helps

Do you think this should have been handled internally within avg itself? I feel like the ignoring-null behavior should be consistent on a simple or compound list.

Agreed @darrenwsun! I'll raise this internally to see what the core q guys think 🙂

avg/min/max/sum where implemented similar to SQL92 as per the note here.

From what I gather, there are some optimizations available for a vector that aren't available for a slice, hence the functionality we see today, and trying to change this would be difficult now as you might, (and probably will) see different results in an existing system.

Thanks David for following up. 

Ignoring null makes perfect sense, especially for avg/max/sum. Imagine otherwise, the result would be null as long as the input contains one null, effectively forcing everyone to explicitly coalesce. 

I understand such change is not backward compatible, and given the ability to manually exclude null like what you shared, I'd agree it's not worth doing it. However I'm curious if we could leave legacy burden behind, what would be the decision from language designers.

perfect, exactly what I am looking for. Thanks David.