0

I've got a long MySql string that is intended to calculate a daily percentage of a certain value. However, if there is nothing for a specific day, it just skips that day and goes to the next one. I need it to spit out a "0" for the day that it usually skips. Thanks for your help!

SELECT day(timestamp), CASE when
round(count(w_comp_current_1+W_comp_current_2)*10/86400*100,1) as 'run_time2' iS NULL 
then '0' 
ELSE round(count(w_comp_current_1+W_comp_current_2)*10/86400*100,1) as 'run_time2' END 
FROM location.db WHERE timestamp between subdate(curdate(), interval 1 month) 
and curdate() AND (w_comp_current_1+w_comp_current_2) > 45 
GROUP BY MONTH(Timestamp), DAY(Timestamp) 
ORDER BY Timestamp

New query using calendar table:

Select date_format(calendar.timestamp,'%b-%e') as 'Month-Day', round(count(w_comp_current_1+W_comp_current_2)*10/86400*100,1) as 'run_time2' from calendar 
Left Join courthouse on calendar.timestamp = courthouse.timestamp 
WHERE calendar.timestamp between subdate(curdate(), interval 1 month) and curdate() and calendar.timestamp > '2013-10-03%'  AND (w_comp_current_1+w_comp_current_2) > 45 
GROUP BY MONTH(calendar.Timestamp), DAY(calendar.Timestamp) ORDER BY calendar.Timestamp
user2686567
  • 23
  • 1
  • 5

1 Answers1

1

You have the alias for the case column twice and both times in the wrong place. It should only be given after the case's END statement:

SELECT day(TIMESTAMP), CASE 
    WHEN round(count(w_comp_current_1 + W_comp_current_2) * 10 / 86400 * 100, 1) IS NULL 
      THEN '0'
    ELSE round(count(w_comp_current_1 + W_comp_current_2) * 10 / 86400 * 100, 1) 
    END AS 'run_time2' 
FROM location.db
WHERE TIMESTAMP BETWEEN subdate(curdate(), interval 1 month) AND curdate()
  AND (w_comp_current_1 + w_comp_current_2) > 45
GROUP BY MONTH(TIMESTAMP), DAY(TIMESTAMP)
ORDER BY TIMESTAMP
Filipe Silva
  • 21,189
  • 5
  • 53
  • 68
  • Ok. That works. But it still does not give me a '0' for the day when there was no calculated value. Maybe "if null" is not the correct approach? – user2686567 Nov 12 '13 at 19:43
  • Actually, the day(timestamp) does not need to be limited by the "(w_comp_current_1 + w_comp_current_2) > 45" where clause. Because I want every day to show up. – user2686567 Nov 12 '13 at 19:47
  • That is other issue completely. You have to use something like a calendar table in order for your query to return values for the days with no records see [this tutorial](http://www.artfulsoftware.com/infotree/qrytip.php?id=95) on calendar tables and search for them in stackoverflow. You will find lots of examples to follow – Filipe Silva Nov 12 '13 at 19:50
  • Ah thank you. So the only way around this is to create another table. – user2686567 Nov 12 '13 at 19:59
  • It't not the only way. It's one way :p. See for example [this](http://stackoverflow.com/q/9499262/1385896) other question. It has a couple of solutions – Filipe Silva Nov 12 '13 at 20:03