Join it to a table that has all the hours and to the query with an outer join.
----- Original Message ----- From: "Almar van Pel" <[EMAIL PROTECTED]> To: "Mysql" <[EMAIL PROTECTED]> Sent: Saturday, February 09, 2002 11:00 AM Subject: Is it a bug / not implemented /me ? > Hi, > > This morning is posted a message : > ---&&&& > I have a question about a query. We have a guestbook and I want to do some > statistics for the messages in it. So I've created a query that looks like > this. > > select hour(msg_date_time), count(*) > from messages > where user_id = 'almar' > group by hour(msg_date_time) > > It returns the hour and the number of messages posted in that hour. However, > there are hours where no messages have been posted, so the result would look > like this: > > hour msg_count > 0 10 > 1 15 > 3 6 > > > So in the example between 2 a clock and 3 a clock no messages have been > posted. But I do want it to return 0 for hour 2. I know that my query will > never return that result. We are using perl DBI. Does anyone have an idea? > ------&&&& > > Ok i've got some great reactions on it, however when testing it, it does not > seam to work correct. > Below i've made a dump. As you can see, it doesn't touch the index on te > user_id, wich works fine in other query's. That's why it takes 20 seconds to > do the query. (0,01 second in all the other query's, without the join) > > And second of all it does not use the all fields in the table uren (wich > stands for hours) as you can see in the output below the hour 3 is missing, > > mysql> explain > -> select uren.uur, > -> count(messages.msg_date_time) > -> from uren left join messages > -> on hour(messages.msg_date_time) = uren.uur where messages.user_id = > 'almar' group by uren.uur > -> ; > +----------+-------+---------------+-----------+---------+------+--------+-- > ----------------------------+ > | table | type | possible_keys | key | key_len | ref | rows | > Extra | > +----------+-------+---------------+-----------+---------+------+--------+-- > ----------------------------+ > | uren | index | NULL | PRIMARY | 4 | NULL | 24 | > Using index; Using temporary | > | messages | index | NULL | date_time | 16 | NULL | 237765 | > where used; Using index | > +----------+-------+---------------+-----------+---------+------+--------+-- > ----------------------------+ > 2 rows in set (0.00 sec) > > mysql> select uren.uur, > -> count(messages.msg_date_time) > -> from uren left join messages > -> on hour(messages.msg_date_time) = uren.uur where messages.user_id = > 'almar' group by uren.uur > -> ; > +-----+-------------------------------+ > | uur | count(messages.msg_date_time) | > +-----+-------------------------------+ > | 0 | 4 | > | 1 | 5 | > | 2 | 3 | > | 4 | 1 | > | 5 | 1 | > | 6 | 1 | > | 7 | 2 | > | 8 | 4 | > | 9 | 8 | > | 10 | 12 | > | 11 | 15 | > | 12 | 17 | > | 13 | 15 | > | 14 | 17 | > | 15 | 15 | > | 16 | 21 | > | 17 | 19 | > | 18 | 17 | > | 19 | 26 | > | 20 | 19 | > | 21 | 22 | > | 22 | 14 | > | 23 | 18 | > +-----+-------------------------------+ > 23 rows in set (20.84 sec) > > > Who can help me out on this one? Or is it not implemented, the way i use it? > > Thanks in advance > > Regards, > > Almar van Pel > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
