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
