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

SELECT WeekDay, AVG(Count)
FROM myTable
WHERE Count IS NOT NULL
GROUP BY WeekDay

Answer 2

Use IsNULL(Count,0) in your Select

SELECT WeekDay, AVG(IsNULL(Count,0))
FROM myTable
GROUP BY WeekDay

Answer 3

First off, you need to get the number of instances of that weekday in the data since you just need the last 3 same week days

create table table2
as
select
    row_number() over(partition by weekday order by date desc) as rn
   ,weekday
   ,date
   ,count
from table

From here, you can get what you want. With you explanation, you don't need to filter out the NULL values for count. Just doing the avg() aggregation will simply ignore it.

select
    weekday
   ,avg(count)
from table2
where rn in (1,2,3)
group by weekday

Answer 4

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.