mysql count row occurances in a date range, but convert null to 0 so it shows

Question

This is my current query

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 count exists.

I was mucking around with ifnull, but no luck

Im sure this is an easy one, but i can't figure it out!

Any help would be so appreciated

Answer 1

MySQL lacks a way to generate a recordset in runtime (like generate_series in PostgreSQL).

The only way to do this query is to keep around a table of all possible dates (or, better, a smaller table you can cross-join with itself):

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 left join your table with it:

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 records".

Answer 2

Welcome Doug! I ran a modified version of your SQL locally and I'm getting results even for those NULL dates. First things first - to real-time convert a null into some other value ("convert null to 0") you need to use the MySQL statement IF, if you know anything of Oracle, it's a lot like the DECODE command. A NULL value automatically evaluates to false, so you can simply write:

SELECT IF(date_created,date_created,0) FROM sometable

Of course... 0 isn't anymore a date then NULL is. I found the DAYNAME function simply passes on NULL dates for you to deal with:

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'm getting out of that (with an example data set) is 8 values of day: 7 days of the week + NULL (with a count). Which kinda makes sense... you can't convert an unknown date into a day of the week.

It could have been your errant @ signs in your SQL, unless I'm misunderstanding your purpose.

UPDATE

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

Source: day-counting in PHP

//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 3

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 would want to include date_created if it's null. Maybe some sample rows and desired output would help in illustrating exactl what you're looking for. Good luck! :)