SQL and IfNull

General
1

Given : a database table (SQLite) with two columns
READING_DT : format is string YYYYMMDD [too late to change this, so don’t bother mentioning SQLDATETIME format :slight_smile: ]
GLUCOSE : Numeric Value (which CAN BE NULL)

Desired Result… A “x-day” moving average (example below is 2 day)
There can be from 0 to N values with the same date… and some of those dates might have a NULL Glucose value (there are other fields in the table)

select a.reading_dt,ifnull(a.glucose,0)
        , (select AVG(b.glucose) 
             from zref_info_health b
            where ifnull(b.glucose,0)>0
            and 
            date(substr(b.reading_dt,1,4)||'-'||substr(b.reading_dt,5,2)||'-'||substr(b.reading_dt,7,2))
            between 
            date(substr(a.reading_dt,1,4)||'-'||substr(a.reading_dt,5,2)||'-'||substr(a.reading_dt,7,2),'-2 days')
            and 
            date(substr(a.reading_dt,1,4)||'-'||substr(a.reading_dt,5,2)||'-'||substr(a.reading_dt,7,2))
            ) mov_avg
from zref_info_health a
 where ifnull(a.glucose,0)>300
order by a.reading_dt

This seems to work… EXCEPT that it is counting the NULLS, which throws off the count used in calculate AVG

Example

20150209  null
20150211 350
20150211 311
20150212 324
20150212 249

results SHOULD be

20150211 350     // (350)/1
20150211 330.5  // (350+311)/2
20150212 308.5 // (350+311+324+249)/4

What I am getting is

20150209  null    // should not even be here
20150211 220.3   //(350+311)/3
20150211 220.3   //(350+311)/3
20150212 308.5 // (350+311+324+249)/4

and other random check thru out the current data (spans last few months) shows similar incorrect results, so it doesn’t seem to be limited to just the first few records

all the substr stuff is to convert the STRING date (20150209) to a date the can be decremented and compared, and THAT seems to be working correctly

2

Why not just write the query to ignore the null glucose readings entirely rather than treat them as 0 values ?
That seems to be what you’re intending
Basically add a “not null” check to the queries involved

3

Or just code it

where b.glucose > 0

It will skip any null values. A null compared to any value is always false. Even nullvalue = othernullvalue. False.

4

[quote=174584:@Norman Palardy]Why not just write the query to ignore the null glucose readings entirely rather than treat them as 0 values ?
That seems to be what you’re intending
Basically add a “not null” check to the queries involved[/quote]
That was the first thing I tried… didn’t work, nor did the version above

5

I’ve just created this two-column table, with the same values as you, and used the exact same copy-pasted query from your initial post to pull out the averages, and mov_avg is coming out ok. I’m using SQLiteManager to do this. I’ve not tried it in Xojo itself, but I’d be surprised if there were a different result.
My guess is that something fishy is going on. Can you check b.glucose is actually 0, not >0 (e.g. put in a ‘CASE WHEN b.glucose = 0 THEN ‘ZERO’ ELSE ‘NOT ZERO’ END’ next to when you’re selecting AVG(b.glucose) ). Or, what happens if you make a brand-new table, with just the values in that you’ve put above, and rerun the query?
H

6

this works (was missing GROUP BY)

select a.reading_dt,ifnull(a.glucose,0)
        , (select sum(b.glucose) 
             from zref_info_health b
            where b.glucose>0
            and 
            date(substr(b.reading_dt,1,4)||'-'||substr(b.reading_dt,5,2)||'-'||substr(b.reading_dt,7,2))
            between 
            date(substr(a.reading_dt,1,4)||'-'||substr(a.reading_dt,5,2)||'-'||substr(a.reading_dt,7,2),'-2 days')
            and 
            date(substr(a.reading_dt,1,4)||'-'||substr(a.reading_dt,5,2)||'-'||substr(a.reading_dt,7,2))
            ) mov_avg
from zref_info_health a
 where a.glucose>0
group by a.reading_dt
order by a.reading_dt

thanks