MySQL is counting the number of rows in a date range, but converting null to 0, so it shows

Question

This is my current request.

SELECT DAYNAME(date_created) AS Day, COUNT(*) AS my_count FROM sometable WHERE (@date_created >= '2010-10-20 21:02:38' OR @date_created IS NULL) AND (@date_created <= '2010-10-27 21:02:38' OR @date_created IS NULL) GROUP BY DAY(date_created) 

It only returns data for that day if the counter exists.

I cheated ifnull but no luck

I am sure it is easy, but I can not understand it!

Any help would be greatly appreciated.

Answer 1

Use COALESCE:

SELECT COALESCE (x, 0) AS val FROM table;

if x is null, it will return 0

Answer 2

MySQL lacks a way to create a set of records at runtime (e.g. generate_series in PostgreSQL ).

The only way to make this query is to keep a table of all possible dates (or, better, a smaller table that you can cross-join yourself):

 CREATE TABLE a (a INT PRIMARY KEY); INSERT INTO a VALUES (0), (1), … (99) 

Then you can generate a list of all possible dates and leave it in the table:

 SLEECT '2010-01-01' + INTERVAL 10000 * a1.a + 100 * a2.a + a3.a DAY AS dt, COUNT(date_created) FROM a a1 CROSS JOIN a a2 CROSS JOIN a a3 LEFT JOIN sometable ON date_created = '2010-01-01' + INTERVAL 10000 * a1.a + 100 * a2.a + a3.a DAY WHERE (a1.a, a2.a, a3.a) <= (0, 3, 65) GROUP BY dt 

This condition:

 (a1.a, a2.a, a3.a) <= (0, 3, 65) 

means "take 365 entries."

Answer 3

Welcome Doug! I ran a modified version of your SQL locally, and I get results even for those NULL dates. First, first - in real time, convert zero to another value ("convert zero to 0"), you need to use the MySQL IF statement , if you know anything about Oracle, this is very similar to the DECODE command. NULL is automatically evaluated as false, so you can simply write:

 SELECT IF(date_created,date_created,0) FROM sometable 

Of course ... 0 is no longer a date, but NULL. I found that the DAYNAME function just passes NULL dates for you:

 SELECT DAYNAME(date_created) day,COUNT(*) my_count FROM sometable WHERE date_created IS NULL OR (date_created>='2010-10-20 21:02:38' AND date_created <= '2010-10-27 21:02:38') GROUP BY DAY(date_created) 

What I get from this (with an example dataset) is 8 day values: 7 days of the week + NULL (with a score). Which view makes sense ... you cannot convert an unknown date to a day of the week.

Perhaps these were your erroneous @ signs in your SQL, if I do not understand your purpose.

UPDATE

Based on your last comment, you need to take on PHP processing. Create an array of days before processing, then add MySQL data to the existing array so that all days are (and in order).

Source : PHP time counting

 //You may not even need "date_created IS NULL OR" in the where statement $sql="SELECT DAYNAME(date_created) day,COUNT(*) my_count FROM sometable WHERE date_created IS NULL OR (date_created>='2010-10-20 21:02:38' AND date_created <= '2010-10-27 21:02:38') GROUP BY DAY(date_created)"; //We're assuming you've setup a MySQL connection in $conn $result = @mysql_query($sql, $conn) or die(mysql_error()); //Starting data - Zero for every day $days=array("Sunday"=>0,"Monday"=>0,"Tuesday"=>0,"Wednesday"=>0, "Thursday"=>0,"Friday"=>0,"Saturday"=>0); while($row = mysql_fetch_array($result)) { $days[$row["day"]]+=$row["my_count"]; } mysql_free_result($result); //Preview the output print_r($days); 

Answer 4

The @ sign describes a user variable - are you pulling date_created from sometable? If so, remove the @ sign from the variables.

Not sure why you want to include date_created if it is null. Perhaps some sample strings and the desired result will help you determine exactly what you are looking for. Good luck! :)

Answer 5

Be consistent when using @