Question 112956 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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!

Tags:

2

Answers


  1. 0

    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';
    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search