E
E
entermix2016-08-02 20:34:17
MySQL
entermix, 2016-08-02 20:34:17

How to fill the sample result with gaps?

There is a request:

SELECT `date`, SUM(`visits`) as `sum`, DATE_FORMAT(FROM_UNIXTIME(`date`), '%M %Y') as `period` FROM `statistics` WHERE `е_id` = 10 GROUP BY `period`  ORDER BY `date` ASC

We get the following result:
date 	sum 	period 	
1453323600 	7 	January 2016
1456693200 	4 	February 2016
1456779600 	9 	March 2016
1461099600 	11 	April 2016
1464728400 	12 	June 2016
1468443600 	5 	July 2016

date is UNIX_TIMESTAMP(CURDATE()), i.e. 1 day - 1 record
There is no information for May here (because there were simply no visits, which means that the statistics were not recorded). After all, it's not very good to make a useless record in the database, if there are zeros anyway, besides, if the database trigger writes?
Is it possible to get such a selection in a native way in MYSQL, only to be like this?
date 	sum 	period 	
1453323600 	7 	January 2016
1456693200 	4 	February 2016
1456779600 	9 	March 2016
1461099600 	11 	April 2016
1464123600 	0 	May 2016 // Информация за май
1464728400 	12 	June 2016
1468443600 	5 	July 2016

That is, so that even if there is no information in the table for a certain month, after the selection, we have it in the form of filled zeros. This is necessary in order to display a graph in c3js so that it can be clearly seen in which month there are no visits at all (maybe this can be done using c3js?). The same is needed when selecting by day, now you have to fill in this information on the side of the site, which is somehow not very convenient.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
A
Alexander Aksentiev, 2016-08-02
@entermix

first form an empty array in which all dates = zeros.
Then fill it with a selection from the database.

K
kretsu, 2016-08-04
@kretsu

I think (and this solution is used in real projects) you need a table for the calendar, in business systems this is also done to mark working / non-working days.
by linking your table with the calendar table, you can normally get "skips"
one problem - the calendar must be supported.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question