Query between Mysql dates showing all days of range

Asked

Viewed 152 times

3

I have a system of e-commerce in which N products are sold and I am making a comparison between sales of a particular product per period of time, ie the admin will have an area in which he chooses a product and a crease date (ex: 01/04/2018 - 26/04/2018) to plot this in a line chart making it possible to see the sales evolution of this product over time.

Is there any way, in Mysql itself, to make even dates that have no sales, appear as 0? I know I could generate this range with PHP and search 1 to 1 and generate a array but wanted to know if there is any direct way in Mysql.

Example:

Produto A
Data Inicial: 01/04/2018
Data Final: 05/04/2018

01/04 - 01 vendidos
02/04 - 04 vendidos
03/04 - 00 vendidos
04/04 - 06 vendidos
05/04 - 00 vendidos

The result of this query would be only 

01/04 - 01 vendidos
02/04 - 04 vendidos
04/04 - 06 vendidos

I wish the days were dead, too.

mysql

  • Does a total SELECT COUNT(*) AS no longer show that amount 0?

  • I found this topical on the SOEN https://stackoverflow.com/questions/1046865/mysql-select-all-dates-in-a-range-even-if-no-records-present

  • http://www.media-division.com/using-mysql-generate-daily-sales-reports-filled-gaps

  • Guys, I treated in PHP itself, but the idea of SOEN that Pedro posted may be that it works, the Leo needs process, that tbm would not be interesting, easier to deal with in the application. Marcolla M, because in this case only those that had lines to count would appear.

2 answers

0

Old question but currently there is a more elegant solution supported from Mysql version 8, using a common table Expression (clause WITH) recursive.

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 5
    )
SELECT n 
FROM cte_count
# left join com os dias ...
;

0

What you need to do is query the intended range with Mysql and then with PHP you do the interaction and show every day and conditions so that days that are not returned are displayed as 0.

WHERE DATE(data) > DATE(:inicial) AND DATE(data) < DATE(:final)

Browser other questions tagged mysql

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