Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- KX Community
- :
- Discussion Forums
- :
- kdb+ and q
- :
- select avg (ignore null)

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Options

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

2022.06.29 12:34 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

2022.06.29 12:59 AM

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

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

2022.06.29 12:59 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

2022.06.29 01:18 AM

`avg`

itself? I feel like the ignoring-null behavior should be consistent on a simple or compound list.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

2022.06.29 01:50 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

2022.06.29 04:49 PM - edited 2022.06.30 08:07 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

2022.06.30 09:38 AM - edited 2022.06.30 09:40 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

2022.06.29 01:37 AM

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

Main Office Contacts

**EMEA**

Tel: +44 (0)28 3025 2242

**AMERICAS**

Tel: +1 (212) 447 6700

**APAC**

Tel: +61 (0)2 9236 5700

Useful Information

Resources

Popular Links

Follow Us

KX. All Rights Reserved.

KX and kdb+ are registered trademarks of KX Systems, Inc., a subsidiary of FD Technologies plc.