MySQL - Average ignoring Null and based on weekday

Question

I´m trying to do some analysis in the following data

WeekDay Date    Count   
5   06/09/2018  20  
6   07/09/2018  Null    
7   08/09/2018  19  
1   09/09/2018  16  
2   10/09/2018  17  
3   11/09/2018  24  
4   12/09/2018  25  
5   13/09/2018  24  
6   14/09/2018  23  
7   15/09/2018  23  
1   16/09/2018  9   
2   17/09/2018  23  
3   18/09/2018  33  
4   19/09/2018  22  
5   20/09/2018  31  
6   21/09/2018  17  
7   22/09/2018  10  
1   23/09/2018  12  
2   24/09/2018  26  
3   25/09/2018  29  
4   26/09/2018  27  
5   27/09/2018  24  
6   28/09/2018  29  
7   29/09/2018  27  
1   30/09/2018  19  
2   01/10/2018  26  
3   02/10/2018  39  
4   03/10/2018  32  
5   04/10/2018  37  
6   05/10/2018  Null    
7   06/10/2018  26  
1   07/10/2018  11  
2   08/10/2018  32  
3   09/10/2018  41  
4   10/10/2018  37  
5   11/10/2018  25  
6   12/10/2018  20  

The problem that I want to solve is: I want to create a table with the average of the 3 last same weekdays related to the day. But, when there is a NULL in the weekday, I want to ignore and do the average only with the remain numbers, not count NULL as an 0. I will give you an example here:

The date in this table is day/month/year :)

Ex: On day 12/10/2018, I need the average from the days 05/10/2018; 28/09/2018; 21/09/2018. These are the last 3 same weekday(six) as 12/10/2018. . Their values are Null; 29; 17. Then the result of this average must be 23, because I need to ignore the NULL, and not be 15,333.

How can I do this?

Answer 1

The count() function ignores nulls (i.e. does NOT increment if it encounters null) so I suggest you simply count the values then may contain the nulls you wish to ignore.

dow datecol     value
6   21/09/2018  17
6   28/09/2018  29
6   05/10/2018  Null

e.g. sum(value) above = 46, and the count(value) = 2 so the average is 23.0 (and avg(value) will also return 23.0 as it also ignores nulls)

select
      weekday
    , `date`
    , `count`
    , (select (sum(`count`) * 1.0) / (count(`count`) * 1.0)
       from atable as t2
       where t2.weekday = t1.weekday
       and t2.`date` < t1.`date
       order by t2.`date` DESC
       limit 3
      ) as average
from atable as t1

You could just use avg(count) in the query above, and get the same result.

ps. I do hope you do NOT use count as a column name! I also would suggest you do NOT use date as a column name either. i.e. Avoid using SQL terms as names.