I've got a very simple table which contains records of events. It's just a table with 2 columns: an id and a timestamp. Every time this event occurs, I insert a timestamp in the table.
I use this query to extract the number of events on each separate day:
SELECT COUNT(id) AS number, FROM_UNIXTIME(UNIX_TIMESTAMP(stamp),"%d-%m-%Y") AS date, FROM_UNIXTIME(UNIX_TIMESTAMP(stamp),"%Y%m%d") AS sort FROM koffiestats GROUP BY date ORDER BY sort ASC
This gives me something like this: +--------+------------+----------+ | number | date | sort | +--------+------------+----------+ | 5 | 02-07-2003 | 20030702 | | 7 | 03-07-2003 | 20030703 | | 6 | 04-07-2003 | 20030704 | | 5 | 07-07-2003 | 20030707 | | 2 | 08-07-2003 | 20030708 | | 5 | 09-07-2003 | 20030709 | +--------+------------+----------+
In this set of records, you see that there are days that nothing happened (this is usually in weekends and holidays).The problem is, that I want to see a '0' when no event occurred.
Can I achieve this with a different query?
Kind regards, \Olaf
-- __XXX__ (0-0) +--ooO--(_)--Ooo-------------------------------------------------------+ | | | Olaf van Zandwijk | | | | ICQ# 30231605 | | PGP Public Key: http://www.vanzandwijk.net/pgp.txt | +----------------------------------------------------------------------+
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]