How to fill empty value when no record in that day using MySQL group by


Problem :

SELECT `Date`, SUM(Clicks) AS Clicks, DAY(LAST_DAY(NOW()))  AS Monthdays
FROM myTbl 
WHERE ( DATE BETWEEN DATE_FORMAT(NOW(), '%Y-%m-01') AND NOW() )      
AND MoverID = 123  GROUP BY `Date` ASC 

I don't want use PROCEDURE like this: http://stackoverflow.com/questions/7252460/mysql-group-by-and-fill-empty-rows

I don't want to create a whole day containing table like this: http://stackoverflow.com/questions/5988179/mysql-group-by-date-how-to-return-results-when-no-rows

I can use SELECT @@global.time_zone, @@session.time_zone; in PHP and after the MySQL query result is out, I make PHP to the same timezone as MySQL, and fill the date by PHP. But is it a way I can just do it in MySQL way?



Solution :

You can use a trick to generate virtual table having all the dates you need with another table (replace aux with any table in your DB with 31 recored at least):

SELECT CONVERT(@d := DATE_ADD(@d, INTERVAL 1 DAY), DATE) AS `d`
FROM
    `aux`,
    (SELECT @d := DATE_SUB(CONVERT(DATE_FORMAT(NOW(), '%Y-%m-01'), DATETIME), INTERVAL 1 DAY)) `x`
WHERE
    @d < DATE_SUB(NOW(), INTERVAL 1 DAY)
LIMIT
    31

And then join you table on it:

SELECT
    `aux`.`d` as `Date`,
    SUM(IFNULL(`Clicks`, 0))AS `Clicks`,
    DAY(LAST_DAY(NOW())) AS `Monthdays`
FROM (
    SELECT CONVERT(@d := DATE_ADD(@d, INTERVAL 1 DAY), DATE) AS `d`
    FROM
        `aux`,
        (SELECT @d := DATE_SUB(CONVERT(DATE_FORMAT(NOW(), '%Y-%m-01'), DATETIME), INTERVAL 1 DAY)) `x`
    WHERE
        @d < DATE_SUB(NOW(), INTERVAL 1 DAY)
    LIMIT
        31
) aux
LEFT JOIN
    myTbl
    ON `Date` = `aux`.`d`
GROUP BY `aux`.`d`

Mysql Tutorials

Mysql Howto..

how to create role on MySQL database

How to select distinct rows from SQL Server table

How does one detect a non-contiguous number column in a MySQL query?

In MySQL how to write SQL from two tables?

how to perform update query using surrogate key

how to separate mysql cell in PHP by given symbol

MySQL, how to sort according to categories?

How to pass the value of javascript into a PHP variable and stored into mysql database? [duplicate]

How to handle user assets after deletion

How to load data from MySql database into list(Console-Based app)?

MySQL - Perl- Order by - How to display ABS(PartNum) DESC but, three different (PartNum) categories?

How to properly GROUP BY in MySQL?

How to implement a (variable!) map into website

how to handle associative array in php

How to get each day's information using group by keyword in mysql

MySQL - How to count number of rows from primary query, ignore subquery rows?

MySQL, how to get the largest value of one column?

How to prevent/check for data loss when converting MySQL tables from Latin1 to UTF8

How to add a LEFT JOIN to this MySQL Query?

How can I show the counts of distinct values and include zeros?

How to group by in MySQL?

How to send account recharge notifications to users, PHP+MySQL

How to save quotes in MySQL database

how to get value of each row in php/mysql where condition is by it's category

How to turn off MySQL strict mode in Rails 3.2.x

How to fetch the unmapped records in mysql

How to efficiently get a random row out of a massive table? [duplicate]

How to select Unique value with some condition for mysql?

How can I perform multi-table delete in safe mode with binary logging enabled?

How do I find and replace individual strings in a MySQL database?