Getting '0' results from MySQL COUNT()

[lostcommander]

I have a table with a timestamp column. I would like to get the count of the number of rows in the table corresponding to each of the past 7 days. My problem is that I get no row if there are none, instead of {[date], 0}. Can anyone provide me with a clever solution to this?

Current MYSQL query:

SELECT DATE(submit_dt) AS date, COUNT(id)
FROM db_itemtable
WHERE DATE(submit_dt) > DATE(TIMESTAMPADD(DAY,-4,NOW()))
GROUP BY date;

Example current return:
2009-10-25, 2
2009-10-24, 11
2009-10-22, 6

What I want returned:
2009-10-25, 2
2009-10-24, 11
2009-10-23, 0
2009-10-22, 6

After I have the existing query, I can use PHP to add in the empty rows, but that seems a bit wasteful and inelegant.

 

[playminigames]

i would just stick to the solution that you are using for now, because if what im thinking is right it would take a lot more coding, and it would just be easier to use what your using now.

 

[suomiaol]

I was working on this for a minute, and then I finally figured it out... (heh, yes I'm sleepy that is why this took a while):

The problem is with your where clause, if you have zero records per day your SQL will not return any rows. If you have a timestamp for a day it will automatically return count >=1 (right?)

In general for casting a null value you can use e.g. COALESCE - function, ISNULL or IFNULL.

 

[misson]

The problem is with your where clause, if you have zero records per day your SQL will not return any rows. If you have a timestamp for a day it will automatically return count >=1 (right?)

In general for casting a null value you can use e.g. COALESCE - function, ISNULL or IFNULL.

The problem isn't a NULL value, it's (as you note in the first sentence above) lack of rows. COALESCE, IFNULL & al. won't help. Not much will help. The OP would need to generate rows for the dates, and SQL is more for manipulating data than generating it. For this reason, an SQL solution will most likely be unwieldy or inelegant. With a table of dates from over the week, it could be simple, but that begs the question.

 

[descalzo]

SELECT COUNT( * ) AS count, 
       ADDDATE( CURDATE(), 0)  AS date
FROM `testing` 
WHERE DATE( submit_dt ) = ADDDATE( CURDATE(), 0)
 

UNION
 
SELECT COUNT( * ) AS count, 
       ADDDATE( CURDATE(), -1)  AS date
FROM `testing` 
WHERE DATE( submit_dt ) = ADDDATE( CURDATE(), -1)
 
UNION
 
SELECT COUNT( * ) AS count, 
       ADDDATE( CURDATE(), -2)  AS date
FROM `testing` 
WHERE DATE( submit_dt ) = ADDDATE( CURDATE(), -2)
 
UNION
 
SELECT COUNT( * ) AS count, 
       ADDDATE( CURDATE(), -3)  AS date
FROM `testing` 
WHERE DATE( submit_dt ) = ADDDATE( CURDATE(), -3)

Assuming you are starting with today and want four days total.
Can be generated by a PHP loop.