0

I been trying to make this one work, but can't find a solution.

I want to grab the SUM of a row for a given date, and if there is no entry for that date, return a 0. I tried using IFNULL() but can't make it work.

My query grabs the data for the last 15 dates and SUMS the content for each date. Here is the query

SELECT date(left(date_column, 10)) as dd, ROUND(SUM(sum_total)/1000)
as cnt FROM order_table WHERE date(left(date_column, 10)) >= DATE_ADD(CURDATE(), INTERVAL -14 DAY) 
GROUP BY date(left(date_column, 10)) ORDER BY dd ASC

I tried

SELECT date(left(date_column, 10)) as dd, IFNULL ( ROUND(SUM(sum_total)/1000) ,'0') 
FROM order_table WHERE date(left(date_column, 10)) >= DATE_ADD(CURDATE(), INTERVAL -14 DAY) 
GROUP BY date(left(date_column, 10)) ORDER BY dd ASC

My problem occurs when there is no data for a given date and therefore the date is skipped.

Any tip in the right direction will be appreciated

JordanBelf
  • 3,208
  • 9
  • 47
  • 80
  • I would do this in code instead of SQL. Really not even sure how you would do it in SQL. – CptMisery Jan 19 '17 at 21:22
  • Your `group by` resultset does not contain dates the original data doesn't have, so there is nothing `ifnull` would work on. You have to add rows with the missing dates. Try e.g. [MySQL how to fill missing dates in range?](http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range) (or any similar solution) with your resultset. – Solarflare Jan 20 '17 at 03:37

1 Answers1

0

I would try using a regular IF function and test for null that way.

http://www.w3resource.com/mysql/control-flow-functions/if-function.php

ale8oneboy
  • 127
  • 4
  • Thanks, the thing is that date or count won't be never null, just not consecutive. I think that is why IFNULL is also not working, because there is a date value, just not consecutive. – JordanBelf Jan 19 '17 at 21:30