How to make MYSQL SELECT "create" the days you don’t have in the table

Asked

Viewed 42 times

1

I’m doing a MYSQL query that returns a download report a day, but in a few days it has no download. For example:

ID|data
1 |2020-05-20
2 |2020-05-21
3 |2020-05-23

In this case, there was no download on the 22nd, so in the result, it would have to bring 22 = 0, is there any way to do this? and also , bring in increasing order the last 7 days

Below is how my query is:

SELECT count(id) AS downloads, DATE_FORMAT(data, '%d/%m/%Y') as dataformat, DATE_FORMAT(data, '%d/%m') as dia
FROM downloads
GROUP BY dataformat
ORDER BY data DESC
LIMIT 7
mysql select
  • 1

    I’ve seen about three questions with the same logic here, with dates, days of the week... if you give some examples of this ;)

  • to group, I managed to find a way, but I could not fill with 0, the days that have no download

1 answer

0

From Mysql version 8 is possible with a common table Expression (clause WITH) recursive.

I isolated only the stretch to popular the days between 1 and 30, from there can do the Join with the rest of the consultation.

WITH RECURSIVE dias(dia) 
AS (
      SELECT 1
      UNION ALL
      SELECT dia + 1 FROM dias WHERE dia < 30
)
SELECT dia
FROM dias
# left join ...
# continua a consulta
;

Browser other questions tagged mysql select

You are not signed in. Login or sign up in order to post.