Hello Almar > 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?
Data cannot be retrieved from the database, if it has not first been stored there! Your mind holds a firm relationship between a particular time and the number of hours in a day - the computer does not (appear) to have the latter information. Consider: a) add a 'construction' table with the row-values 0 through 11 (or 23) and use it to do a left join against the messages tbl; b) if the database is not busy, use perl to fire off 12 (or 24) separate queries. Regards, =dn --------------------------------------------------------------------- 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
