MYSQL -How to set zero if no row in select date

Question

I want to select value from given date and same time if no row in that date then it should return 0 in that specific date. here is my query

SELECT
    sum(`value`) AS `VALUE`,
    serverTimeStamp AS date
FROM
    table1 t1

WHERE
    serverTimeStamp BETWEEN CONVERT_TZ(
        '2016-03-21 00:00:01',
        '+00:00',
        '-05:30'
    )
AND CONVERT_TZ(
    '2016-03-25 23:30:36',
    '+00:00',
    '-05:30'
)
GROUP BY
    YEAR (date),
    MONTH (date),
    WEEK (date),
    DAY (date)
ORDER BY
    date ASC;

Output :

value date

96 2016-03-21 00:00:01

76 2016-03-23 00:00:01

56 2016-03-25 00:00:01

Expected Output :

value date

96 2016-03-21 00:00:01

0 2016-03-22 00:00:01

76 2016-03-23 00:00:01

0 2016-03-24 00:00:01

56 2016-03-25 00:00:01

Any idea to achieve this?

table1 :

**id value serverTimeStamp**

 1   96 2016-03-21 00:00:01

  2  76 2016-03-23 00:00:01

 3  56 2016-03-25 00:00:01

but what i am expecting in query if there is no row in given date then it should be 0 and specific date like below

value date

96 2016-03-21 00:00:01

0 2016-03-22 00:00:01

76 2016-03-23 00:00:01

0 2016-03-24 00:00:01

56 2016-03-25 00:00:01

score 1 · Answer 1 · edited May 23 '17 at 12:07

You need to create a table storing your possible dates and then LEFT JOIN your query to it, so that every date that is missing from your query would still be included in the output, but with the value 0.

How to generate data into table with dates: Generate series equivalent in MySQL

You just need to alter this query to include an INSERT INTO your table with dates.

Then, depending on how you insert your date (with time - if it's static coming from your query, or without time - if it's changing) you need to join that table with your existing query on the column value if it contains time, or if it doesn't, on the date part extracted from serverTimeStamp from your query

Ankit Agrawal · Answer 2 · 2016-04-08T10:49:29.483

0

Create a stored procedures and do like below to get your desired output

create table temp_data
(
id int,
value int,
server_date datetime 
);

Drop temporary table if exists temp_generate_dates; 
create temporary table temp_generate_dates 
( 
temp_date datetime 
); 

insert into temp_generate_dates 
select adddate('2016-03-21 00:00:01', @num:=@num+1) as date 
from 
temp_data, 
(select @num:=-1) num 
limit 5; 


select coalesce(t1.value,0),t2.temp_date from temp_generate_dates t2 
left join temp_data t1 on t2.temp_date = t1.server_date ;

edited Apr 08 '16 at 10:49

answered Apr 07 '16 at 10:42

Ankit Agrawal

2,426
1
13
27

I am getting "Incorrect usage of UNION and ORDER BY" – MMMMS Apr 07 '16 at 11:43
it not works as expecting, it will return only one row when i check 21 to 25 dates. – MMMMS Apr 07 '16 at 11:59
please update your table's data & architecture in your question. – Ankit Agrawal Apr 07 '16 at 12:01
any idea for this? – MMMMS Apr 08 '16 at 06:37
Add your Table1,Table2,Table3,Table4 data in question – Ankit Agrawal Apr 08 '16 at 06:51
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/108593/discussion-between-mmmms-and-jordan). – MMMMS Apr 08 '16 at 07:08

MYSQL -How to set zero if no row in select date

2 Answers2