for what i understand of this problem, it should not be possible an easy way. once i have found an article on this on the internet, don't know where it was anymore. (neither devshed nor phpbuilder) but it was about JOINing the result with an 'integers table' that has all the needed values from 1 to, say, 31. so you can ensure the appearance of any day value from 1 to 31. of course, this won't work with 30-days-months or even februaries or leap years. in this case i suggest you cover the holes in your processing application logic, drawing a graph or whatever.
-- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) -----Urspr�ngliche Nachricht----- Von: "Olaf van Zandwijk" <[EMAIL PROTECTED]> An: <[EMAIL PROTECTED]> Gesendet: Donnerstag, 21. August 2003 21:38 Betreff: '0' instead of no entry with GROUP BY > Hi everyone, > > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
