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.

MySQL Cookbook: Solutions for Database Developers and Administrators, If not, the expression evaluates to NULL and COUNT() ignores it. The effect is to count the number of values that satisfy the condition given as the first argument  The AVG () function returns the average value of an expression. Note: NULL values are ignored. AVG ( expression) Parameter Values. Required. A numeric value (can be a field or a formula) Technical Details. Select the records that have a price above the average price: SELECT * FROM Products. WHERE Price > (SELECT AVG (Price) FROM Products);

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

MySQL AVG() Function Explained by Practical Examples, This tutorial shows you how to use the MySQL AVG function with many practical examples of how The AVG() function ignores NULL values in the calculation. I want column t to be their weighted average (each of a..f have static weights which I assign), ignoring null values (which can occur in any of them), except being null if they're all null. I would prefer to do this with a simple SQL calculation, rather than doing it in app code or using some huge ugly nested if block to handle every permutation of nulls.

Use IsNULL(Count,0) in your Select

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

Functions for Use in SELECT and WHERE Clauses, If one or both arguments are NULL, the result of the comparison is NULL, IFNULL( ) returns a numeric or string value, depending on the context in mysql​> SELECT WEEKDAY('1997-10-04 22:23:00'); -> 5 mysql> SELECT ENCRYPT​( ) ignores all but the first 8 characters of str, at least on some systems. AVG(​expr). MySQL WEEKDAY() function Last update on February 26 2020 08:08:23 (UTC/GMT +8 hours)

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

MySQL 5.7 Reference Manual :: 12.6 Date and Time , DAYOFWEEK(), Return the weekday index of the argument Functions that expect date values usually accept datetime values and ignore the time part. Other functions expect complete dates and return NULL for incomplete dates. as a value in 'hh:mm:ss' or hhmmss format, depending on whether the function is used in  The WEEKDAY() function returns the weekday number for a given date. Note: 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday. Syntax

Common MySQL Queries, Common Queries,MySQL Queries,Common SQL Queries. Aggregates excluding leaders · Aggregates of Average top 50% values per group Compute date from year, week number and weekday List NULLs at end of query output. SELECT distinct AVG(cast(ISNULL(a.SecurityW,0) as bigint)) as Average1 ,AVG(cast(ISNULL(a.TransferW,0) as bigint)) as Average2 ,AVG(cast(ISNULL(a.StaffW,0) as bigint)) as Average3 FROM Table1 a, Table2 b WHERE a.SecurityW <> 0 AND a.SecurityW IS NOT NULL AND a.TransferW<> 0 AND a.TransferWIS NOT NULL AND a.StaffW<> 0 AND a.StaffWIS NOT NULL AND MONTH(a.ActualTime) = 4 AND YEAR(a.ActualTime) = 2013

SQL AVG() function introduction and examples, This article describes the SQL Average function with examples and illustrations. Also, we As we can see, AVG() considers all weekdays and weekends values in its If we want to ignore duplicate values during the AVG() function AVG() function does not consider the NULL values during its calculation. DAYNAME() function. MySQL DAYNAME() returns the name of the week day of a date specified in the argument. Syntax: DAYNAME(date1) Where date1 is a date.. Syntax Diagram:

MySQL Tutorial - MySQL By Examples for Beginners, "southwind" has no table (empty set). mysql> SHOW TABLES; Empty set (0.00 sec) For example, you can SELECT an expression or evaluate a built-in function. functions (such as COUNT() , AVG() , SUM() ) to produce group summary. -p --all-databases --ignore-table=mysql.user > backupServer.sql -- Dump all the  Unless otherwise stated, group functions ignore NULL values. If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows. For more information, see Section 12.20.3, “MySQL Handling of GROUP BY”. Most aggregate functions can be used as window functions.

Comments

• Where Count is not null
• MySQL should be a tag, not just in the title. But MySQL now has version 8 with new features, so what version of MySQL are you using please?
• Average function also ignores Nulls. Infact, all the aggregation functions ignore nulls. From docs: "Unless otherwise stated, group functions ignore NULL values."
• Thank you for your answer. But I need for each date its avg from the last 3 same weekday. So for example 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. Then, from day 11/10/2018 I want the avg from 04/10/2018, 17/09/2018 and 20/09/2018 and so on. For every day from my data