Get daily average by column even though there are empty dates in MYSQL-CodePudding

I have a table in mysql called trans with the following structure:

trans (
   id number
   date date
   amount number
)

The date is stored in the following format: 2021-09-21

I am trying to make a selection out of this table that will give me a daily average of the amount column.

But not just as simple as select avg(amount), because i'm trying to get the total average even tough there are days in which i don't have any amount.

For example, let's take 5 pairs (date - amount):

2021-09-21 - 100
2021-09-22 - 120
2021-09-24 - 140
2021-09-25 - 300
2021-09-28 - 450

If i have these values in the table, and do the AVG() i would get 1110 : 5 = 222. However, in between these dates there are dates that are empty (23,26,27 th of 09). So the daily avg is not correct, because those days are not take into account, right?

The daily avg should be

2021-09-21 - 100
2021-09-22 - 120
2021-09-23 - 0
2021-09-24 - 140
2021-09-25 - 300
2021-09-26 - 0
2021-09-27 - 0
2021-09-28 - 450

Meaning 1110 / 8 = 138.75.

So i want to select this 138.75 value. Does anyone know a solution for this? I don't want to alter the entries in any way, so I don't want to insert 0 values in between the non empty dates.

Thank you!

CodePudding user response：

You can use Sum() and DateDiff() function to calculate your average in this case. Below SQL query will work for your usecase -

SELECT SUM(amount)/(DATEDIFF('2021/09/28', '2021/09/21')   1) as Avg FROM trans Where date between '2021/09/21' and '2021/09/28';

CodePudding user response：

Well the AVG() function has the property that it ignores NULL values. So if you average the right CASE expression, you should get the behavior you want.

SELECT AVG(CASE WHEN amount > 0 THEN amount END) AS avg_amount
FROM trans;

CodePudding user response：

First 2 user defined variables:

mysql> SET @MinDate = (SELECT MIN(date) FROM trans);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MaxDate = (SELECT MAX(date) FROM trans);
Query OK, 0 rows affected (0.00 sec)

Then SQL Code:

mysql> SELECT SUM(amount) / (DATEDIFF(@MaxDate,@MinDate)   1) FROM trans;
 ------------------------------------------------- 
| SUM(amount) / (DATEDIFF(@MaxDate,@MinDate)   1) |
 ------------------------------------------------- 
|                                        138.7500 |
 ------------------------------------------------- 
1 row in set (0.00 sec)