MySQL - Average ignoring Null and based on weekday
mysql average count
mysql average multiple columns
mysql average multiple rows
mysql avg returns null
mysql avg subquery
syntax for avg in mysql
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?
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
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.
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.
- 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